Building A Better “Where” Clause

Over my career I have learned a lot about SQL Server, unfortunately, sometimes some fundamental ideas get forgotten. This weekend I went to the Jacksonville Code Camp 2009 where I sat in on a session titled “building a better where clause” here are a few of my notes in case anyone forgot of never knew them.

- Try to use indexed fields whenever possible. Searching indexes in much, much faster than un-indexed fields. This means if you are not searching on the primary or foreign key, consider whether the field in questions should either be searched in the first place. If it does, consider adding an index on that field.

- When writing your where clause, take the order into account. Place the expressions most likely to return false first. Take a look at the below queries. Both return the same results, however unless people with the last name “smith” make up 50% of the table the first expression would evaluate faster than the second.

SELECT fname, lname, gender FROM People where lname = ‘smith’ AND gender = ‘male’
SELECT fname, lname, gender FROM People where gender = ‘male’ AND lname = ‘smith’

This is because as the system evaluates each row, deciding if it should be included in the result set, it will move on to the next row as soon as part of the where clause evaluates to false.

- Avoid wrapping table columns listed in the where clause in a function, doing so prevents SQL Server from using any indexes, which means a significant drop in performance.

- Avoid wrapping table columns with upper() or lower() in the where clause unless you SQL server is set to be case sensitive. By default SQL Server is not case sensitive, meaning the functions are not needed. As noted below using these functions prevent the use of indexes.

- Avoid using any form of “NOT”, including not null if possible as all forms of not also prevent indexes from being used.

- When using an in() statement, if possible place the most likely to be found first. Once the comparison evaluates to true the system can immediately move on to the next record.