9 Tips for creating indexes in SQL Server (06 April 2008)

To improve performance in SQL Server the first step is to create indexes on the appropriate table-fields. Below a list of 9 tips when creating indexes.

1) Create indexes on the highly selective colums that are used in the WHERE-clause
2) Create indexes on all columns that are used in the WHERE clause in case OR is used
3) Create at least a clustered index on every table. Generally use the column that monotonically increases
4) Create indexes columns that are frequently accessed by WHERE, ORDER BY, GROUP BY, TOP and DISTINCT
5) Only add indexes that will be used frquently
6) Avoid adding too much indexes on dynamic tables (subject to many INSERTs, UPDATEs or DELETEs)
7) For static tables use a FILLFACTOR and PAD_INDEX of 100. For dynamic tables use a lower FILLFACTOR
8) To identify additional indexes use the SS Profiler Create Trace Wizard and trace "Identify Scans of Large Tables"
9) Avoid adding indexes twice.

Of course another step is to improve your queries. For example make them sargable (capable of using indexes). I've created a list with 23 tips to improve your SQL queries.

These tips (together with lots of other facts) can also be found on my SQL Server factsheet for developers. This factsheet (PDF) fits on one sheet can be found here.

Labels: Indexes, performance, SQL Server, SQL Server 2000, SQL Server 2005

Posted by Xander Zelders
0 Comments

23 Tips to improve the performance of your SQL queries

I've created a list with 23 tips to improve your SQL Server queries. Quite often these tips can also be used for other database-engines like MS Access, Oracle or MySQL.

1) Avoid non-sargable WHERE-clauses. If possible rewrite them to sargable ones
2) In the WHERE-clause use the least likely true AND expression first
3) Avoid using OR in the WHERE-clause if not all colums have an index
4) Avoid using UNION if UNION ALL also does the trick
5) Avoid using UNION of two subsets from the same table. Instead use OR in the WHERE-clause
6) Avoid using SELECT * FROM when only a few columns are needed. Try to specify each column
7) Avoid using COUNT(*) to check the existence of a record. Instead use EXIST
8) Always try to use a WHERE-clause in your query to narrow the results
9) Try to use the best performing operator as possible
10) Avoid using NOT IN. Instead use EXIST, NOT EXIST, IN or LEFT OUTER JOIN with a check for a NULL condition
11) Avoid using IN when EXISTS is also possible
12) Avoid using IN when BETWEEN is also possible
13) In case using IN try to order the list of values so that the most frequently found values are placed first
14) Avoid using SUBSTRING in the WHERE-clause. If possible use LIKE instead
15) Sometimes consider rewriting a query using a OR to multiple queries combined with a UNION ALL
16) Don't use ORDER BY if you don't really need it
17) Keep the width and/or number of sorted columns to the minimum
18) Keep the number of rows to be sorted to a minimum
19) When sorting a specific column often conside making that column a clustered index
20) In case of using HAVING try to minimize the amount of rows using a WHERE clause
21) In case using LIKE on CHAR of VARCHAR colums quite often consider using the full-text search option
22) In case using GROUP BY without an aggregate function try using DISTINCT instead
23) Avoid using variables in a WHERE clause in case the query is located in a batch-file

Thes tips can also be found on my SQL Server factsheet for developers. This factsheet can be found here

Labels: performance, SQL Server, SQL Server 2000, SQL Server 2005

Posted by Xander Zelders
0 Comments