Improve Performance in SQL Server

Marking a column as a primary key will automatically create a unique index on the column.

A term that is often used is "SARGable". This basically refers to a condition in a WHERE clause that is able to use an index if one exists.

Examples of SARGable conditions are: A = x, B LIKE 'AB%', C IN (x, y, z). In each of these the optimizer is able to use an index. Examples of Non-SARGable conditions are: A <> x, B LIKE '%AB', C NOT IN (x, y, z).

If you think about each of these it should be obvious why an index cannot be used. To explain why B LIKE '%AB' is bad, I like to use the analogy of a large dictionary. If you know what letters the word starts with you can find it very quickly. If you only know what letters it ends with you have no option but to read the entire dictionary and check every single word.

You should also avoid using NOT in your WHERE clauses. NOT IN, NOT LIKE and IS NOT NULL perform a scan for exactly the reasons given above. The exception may be NOT EXISTS, which can perform very well for many queries.

One very common mistake is to use functions in such a way that a scan is performed. A condition of the form WHERE Fn(A) = x will not use an index on A, as the function has to be applied to this column in every row before it can be compared with x.

This needs an example to explain properly: suppose you want to match rows added in the last 5 days. I often see this written as follows:

This should be rewritten as follows:

There may be situations in which use of a function is unavoidable. In this case you can add a computed column that uses the function, add an index to the computed column, and refer to the computed column directly in the query.

Another common mistake is due to the way indexes are used in an OR condition. This is described in OR Condition Performance