Today’s post is brought to you by the letter Q. In SQL Server, the biggest thing that Q stands for is Query. Obviously that’s a mighty broad topic, so today I’m just going to go over some best practices for writing queries that I’ve learned over the years.
Nuggets of T-SQL goodness
- List out the columns you need in the SELECT statement. Avoid using SELECT *. Using SELECT * not only adds the overhead of returning columns you may not even need, it might also cause the optimizer to perform a RID lookup when the query might otherwise have been satisfied by just an index read.
- If you’re using EXISTS, use SELECT 1 instead of SELECT *. For example, WHERE EXISTS (SELECT 1 FROM <table> WHERE <columna> = <columnb>).
- Prefix table names with the owner. Not only does this improve the readability of your code, it also helps promote execution plan reuse.
- Whenever possible, avoid using wildcards at the beginning of a search string, as in ‘%Smith’. This forces the optimizer to perform an index scan, rather than an index seek.
- On that same note, avoid using functions on your columns in your WHERE clause. For example, rather than using WHERE left(LastName,2) = ‘Tho’ which will actually cause an index scan, use WHERE LastName LIKE ‘Tho%’. This will allow SQL Server to perform an index seek.
- Avoid using cursors whenever possible. Try to use a set-based approach instead.
- Use comments to document your code. All of your code. You may not think it’s important now, but somewhere, someday, someone is going to read that code and wonder what the heck you were trying to do. And that someone could be you.
- If your query contains more than one table, prefix your column names with the table name or alias, if you’re using them. And if you are using aliases, make them meaningful; none of this t1, t2, t3 stuff.
- When writing your code, keep in mind that it might be executed on a case-sensitive server someday. So use consistent casing when referring to objects. In other words, if the object name is MyTable, refer to it as MyTable in your code, not as mytable.
So there you have it, some of my lessons learned. How about you? What best practices have you adopted?