How to find tables with no indexes

SELECT Schema_Name(Schema_ID) AS SCH_Name ,Name AS TAB_NAME  FROM SYS.TABLES
WHERE OBJECTPROPERTY(Object_ID, 'IsIndexed') = 0
ORDER BY SCH_Name, TAB_NAME

SQL Server Optimization Tips

General Tips

• Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
  This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.
• Try to avoid using SQL Server cursors, whenever possible.
  SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.
• If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT (*) statement.
  Because SELECT COUNT (*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT (*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID ('table_name') AND indid < 2 So, you can improve the speed of such queries in several times.
• Use table variables instead of temporary tables.
  Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.
• Try to avoid using the DISTINCT clause, whenever possible.
  Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.
• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
  This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.
• Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
  This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.
• Try to use UNION ALL statement instead of UNION, whenever possible.
  The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.
• Try to use constraints instead of triggers, whenever possible.
  Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.
• Use user-defined functions to encapsulate code for reuse.
  The user-defined functions (UDFs) contain one or more Transact-SQL statements that can be used to encapsulate code for reuse. Using UDFs can reduce network traffic.
• You can specify whether the index keys are stored in ascending or descending order.
  For example, using the CREATE INDEX statement with the DESC option (descending order) can increase the speed of queries, which return rows in the descending order. By default, the ascending order is used.
• If you need to delete all tables’ rows, consider using TRUNCATE TABLE instead of DELETE command.
  Using the TRUNCATE TABLE is much fast way to delete all tables’ rows, because it removes all rows from a table without logging the individual row deletes.
• Don't use Enterprise Manager to access remote servers over a slow link or to maintain very large databases.
  Because using Enterprise Manager is very resource expensive, use stored procedures and T-SQL statements, in this case.
• Use SQL Server cursors to allow your application to fetch a small subset of rows instead of fetching all tables’ rows.
  SQL Server cursors allow application to fetch any block of rows from the result set, including the next n rows, the previous n rows, or n rows starting at a certain row number in the result set. Using SQL Server cursors can reduce network traffic because the smaller result set will be returned.

Tips for designing Tables

• Try to use constraints instead of triggers, rules, and defaults whenever possible.
  Constraints are much more efficient than triggers and can boost performance. Constraints are more consistent and reliable in comparison to triggers, rules and defaults, because you can make errors when you write your own code to perform the same actions as the constraints.
• Use char/varchar columns instead of nchar/nvarchar if you do not need to store Unicode data.
  The char/varchar value uses only one byte to store one character; the nchar/nvarchar value uses two bytes to store one character, so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.
• If you work with SQL Server 2000, use cascading referential integrity constraints instead of triggers whenever possible.
  For example, if you need to make cascading deletes or updates, specify the ON DELETE or ON UPDATE clause in the REFERENCES clause of the CREATE TABLE or ALTER TABLE statements. The cascading referential integrity constraints are much more efficient than triggers and can boost performance.

Tips for designing Stored Procedures

• Use stored procedures instead of heavy-duty queries.
  This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.
• Call stored procedure using its fully qualified name.
  The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names
• Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
  The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.
• Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
  The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend using the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to execute a Transact-SQL statement that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

Tips for designing Cursors

• Do not forget to close SQL Server cursor when its result set is not needed.
  To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.
• Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed.
  To deallocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.
• Try to reduce the number of columns to process in the cursor.
  Include in the cursor's select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. It can increase cursor performance and reduce SQL Server overhead.
• Use READ ONLY cursors, whenever possible, instead of updatable cursors.
  Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.
• Try avoiding using insensitive, static and keyset cursors, whenever possible.
  These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation.
• Use FAST_FORWARD cursors, whenever possible.
  The FAST_FORWARD cursors produce the least amount of overhead on SQL Server, because there are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.
• Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.
  If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the other cannot be specified.

Tips for Indexes

• Consider creating index on column(s) frequently used in the WHERE, ORDER BY, and GROUP BY clauses.
  These column(s) are best candidates for index creating. You should analyze your queries very attentively to avoid creating not useful indexes.
• Drop indexes that are not used.
  Because each index take up disk space and slow the adding, deleting, and updating of rows, you should drop indexes that are not used. You can use Index Wizard to identify indexes that are not used in your queries.
• Try to create indexes on columns that have integer values rather than character values.
  Because the integer values usually have less size then the characters values size (the size of the int data type is 4 bytes, the size of the bigint data type is 8 bytes), you can reduce the number of index pages which are used to store the index keys. This reduces the number of reads required to read the index and boost overall index performance.
• Limit the number of indexes, if your application updates data very frequently.
  Because each index take up disk space and slow the adding, deleting, and updating of rows, you should create new indexes only after analyze the uses of the data, the types and frequencies of queries performed, and how your queries will use the new indexes. In many cases, the speed advantages of creating the new indexes outweigh the disadvantages of additional space used and slowly rows modification. However, avoid using redundant indexes; create them only when it is necessary. For read-only table, the number of indexes can be increased.
• Check that index you tried to create does not already exist.
  Keep in mind that when you create primary key constraint or unique key constraints SQL Server automatically creates index on the column(s) participate in these constraints. If you specify another index name, you can create the indexes on the same column(s) again and again.
• Create clustered index instead of nonclustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results.
  Because every table can have only one clustered index, you should choose the column(s) for this index very carefully. Try to analyze all your queries, choose most frequently used queries and include into the clustered index only those column(s), which provide the most performance benefits from the clustered index creation.
• Create nonclustered indexes to increase performance of the queries that return few rows and where the index has good selectivity.
  In comparison with a clustered index, which can be only one for each table, each table can have as many as 249 nonclustered indexes. However, you should consider nonclustered index creation as carefully as the clustered index, because each index take up disk space and drag on data modification.
• Avoid creating a clustered index based on an incrementing key.
  For example, if a table has surrogate integer primary key declared as IDENTITY and the clustered index was created on this column, then every time data is inserted into this table, the rows will be added to the end of the table. When many rows will be added a "hot spot" can occur. A "hot spot" occurs when many queries try to read or write data in the same area at the same time. A "hot spot" results in I/O bottleneck.Note. By default, SQL Server creates clustered index for the primary key constraint. So, in this case, you should explicitly specify NONCLUSTERED keyword to indicate that a nonclustered index is created for the primary key constraint.
• Create a clustered index for each table.
  If you create a table without clustered index, the data rows will not be stored in any particular order. This structure is called a heap. Every time data is inserted into this table, the row will be added to the end of the table. When many rows will be added a "hot spot" can occur. To avoid "hot spot" and improve concurrency, you should create a clustered index for each table.
• If you create a composite (multi-column) index, try to order the columns in the key so that the WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.
  The order of the columns in a composite (multi-column) index is very important. The index will be used to evaluate a query only if the leftmost index key's column are specified in the WHERE clause of the query. For example, if you create composite index such as "Name, Age", then the query with the WHERE clause such as "WHERE Name = 'Alex'" will use the index, but the query with the WHERE clause such as "WHERE Age = 28" will not use the index.
• If you need to join several tables very frequently, consider creating index on the joined columns.
  This can significantly improve performance of the queries against the joined tables.
• If your application will perform the same query over and over on the same table, consider creating a covering index including columns from this query.
  A covering index is an index, which includes all of the columns referenced in the query. So the creating covering index can improve performance because all the data for the query is contained within the index itself and only the index pages, not the data pages, will be used to retrieve the data. Covering indexes can bring a lot of performance to a query, because it can save a huge amount of I/O operations.
• Use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes.
  This trace will show which tables are being scanned by queries instead of using an index.

Keyboard Shortcuts in SQL Query Analyzer

CTRL-SHIFT-F2      -- Clear all bookmarks.
CTRL+F2            -- Insert or remove a bookmark (toggle).
F2                 -- Move to next bookmark.
SHIFT+F2           -- Move to previous bookmark.
ALT+BREAK          -- Cancel a query.
CTRL+O -- Connect.
CTRL+F4 -- Disconnect.
CTRL+F4 -- Disconnect and close child window.
ALT+F1 -- Database object information.
CTRL+SHIFT+DEL -- Clear the active Editor pane.
CTRL+SHIFT+C -- Comment out code.
CTRL+C or Ctrl+Insert -- Copy
CTRL+X or Shift+Del -- Cut
SHIFT+TAB -- Decrease indent.
CTRL+DEL -- Delete through the end of a line in the Editor pane.
CTRL+F -- Find.
CTRL+G -- Go to a line number.
TAB -- Increase indent.
CTRL+SHIFT+L -- Make selection lowercase.
CTRL+SHIFT+U -- Make selection uppercase.
CTRL+V or Shift+Insert -- Paste.
CTRL+SHIFT+R -- Remove comments.
F3 -- Repeat last search or find next.
CTRL+H -- Replace.
CTRL+A -- Select all.
CTRL+Z -- Undo.
F5 or Ctrl + E -- Execute a query.
F1 -- Help for Query Analyzer.
SHIFT+F1 -- Help for the selected Transact-SQL statement.
F6 -- Switch between query and result panes.
Shift+F6 -- Switch panes.
CTRL+W -- Window Selector.
CTRL+N -- New Query window.
F8 -- Object Browser (show/hide).
F4 -- Object Search.
CTRL+F5 -- Parse the query and check syntax.
CTRL+P -- Print
CTRL+D -- Display results in grid format.
CTRL+T -- Display results in text format.
CTRL+B -- Move the splitter.
CTRL+SHIFT+F -- Save results to file.
CTRL+R -- Show Results pane (toggle).
CTRL+S -- Save
CTRL+SHIFT+INSERT -- Insert a template.
CTRL+SHIFT+M -- Replace template parameters.
CTRL+L -- Display estimated execution plan.
CTRL+K -- Display execution plan (toggle ON/OFF).
CTRL+I -- Index Tuning Wizard.
CTRL+SHIFT+S -- Show client statistics
CTRL+SHIFT+T -- Show server trace.
CTRL+U -- Use database

SQL SERVER – 2008 – Step By Step Installation Guide With Images

SQL SERVER 2008 Release Candidate 0 has been released for some time and I have got numorous request about how to install SQL Server 2008. I have created this step by step guide Installation Guide. Images are used to explain the process easier.

How to Install SQL Server 2005 - Step by Step - Developer Edition

1. Insert the DVD into the DVD drive and double-click on the icon:



2. The installation screen should appear:



For most installations, if installing on standard 32 bit Windows XP Professional, click on the x86-based operating systems option.

3. Click on Server components, tools, Books online and samples:



4. Read the terms and conditions of the license agreement and click the check box to accept them:



Then click Next.

5. The Installing Prerequisites screen appears:



Click on the Install button and wait whilst the system components are configured. This may take 10-20 seconds.

6. Once the prerequisites are installed, click on the Next:



7. Wait for a few seconds for the following screen to appear, and then click on Next:



8. The System Configuration Check screen appears:



If the configuration check completes successfully (Success appears in the top panel, all ticks in the Details panel), click on Next.

9. Fill in the Registration Information details:



Click on Next.

10. Select the components to install:



Note that the components selected in the screen shot above are the most common components that will be needed for most developers and development DBAs. However, if another component such as Analysis Services is needed, it can be added easily afterwards by re-inserting the installation DVD and re-running the setup program.

Click on the Advanced button to open up further options.

11. The advanced Features Selection screen opens:



IMPORTANT NOTE
As a general rule it is not necessary to change the default installation path or the Developer Edition installation, unless your organization has a standard which is different. If in doubt, check an existing PC or server at your site.

Click on Next.

12. Select the Default instance:



IMPORTANT NOTE
If the Default instance radio button is disabled, this probably means that there is another, default instance of SQL Server 2000 or SQL Server 2005 already installed on this machine. In this case, consult the DBA for the project as it may not be necessary or desirable to re-install the database components.

Click on Next.

13. Select the radio button Use the built-in System account:



The first step will change the Windows account under which the SQL Server service executes to the Local System account. In a production environment or certain development environments, it may be desirable to change this to a domain user account, but this is not usually necessary for local developer installations. Note that the service account settings can be changed through the Services applet in the Control Panel at any point after installation, however SQL Server will require re-starting in order for these changes to take effect.

Click on Next.

14. Change Authentication Mode to Mixed Mode and set a password for the sa logon. The user’s NT logon name could be used as an initial password, but note that whatever is used, it should NOT be the same for each machine as the sa account gives sysadmin privileges to any user who uses it:



Click on Next.

15. Keep the default collation settings, UNLESS the DBA for the project specifies that they should be changed. Be aware that certain applications require non-default settings:



Click on Next.

16. Select Error Reporting and Usage options – it does not matter what these are set to, as they are completely at your discretion:



Click on Next.

17. The pre-installation screen will now appear:



Click on Install.

18. The Setup Progress screen will appear, informing you of installation progress:



This step usually takes 10-30 minutes, but requires no user intervention - so now's a good time to take a break and make some coffee.

19. The Setup steps have completed:



Click on Next.

20. The Completing Microsoft SQL Server 2005 Setup screen will now appear:



If you only want the database server to be accessible from the machine on which it is installed, click on Finish.

SQL Server 2005 installation is now complete.

However, if you wish the database server to be accessible from other machines, click on the Surface Area Configuration tool link.

21. The Help Protect Your SQL Server screen will appear:



Click on the Surface Area Configuration for Services and Connections link.

22. The SQL Server 2005 Surface Area Configuration screen will appear:



Click on the Remote Connections node in the left hand tree pane, then click on Local and remote connections. By default, TCP/IP only is selected and is usually sufficient in most development environments, but if you suspect that you may need SQL Server 2005 to host legacy applications which require support for the named pipes SQL Server communication protocol, click on Using TCP/IP and Named Pipes. If in doubt, consult the DBA for the project.

In addition, if you wish this installation of SQL Server to automatically announce itself on the network to automatic database server directory queries, click on the SQL Server Browser Service, set the Startup type to Automatic, click on Apply, then click on the Start button.

Click on OK, and then close the Help Protect Your SQL Server screen.
Finally, click on the Finish button on the Completing Microsoft SQL Server 2005 Setup screen.