Today I wanted to talk about writing queries so that they are maintainable in the long run. There are two things that I don’t like to see when looking at a select statement.
The first thing you have heard and read over and over, don’t use SELECT *. When you do, * is a variable that has to be resolved when the query plan is generated. It may, or may not, stay in synch when columns are added or removed from a table, view or table value function. So, do yourself a favor; just stay away from using SELECT *.
The second tip for me is to fully qualify the source of data for each column if the query uses more than one table. Often we fully qualify the column source if we are retrieving data from two or more tables having columns with the same name.
This mindset goes beyond just qualifying the columns that may be duplicated in name. I qualify the source on every column returned in my select statements. Using short table aliases it doesn’t take long to type, and with intellisense it is very easy. So why fully qualify? Because when you are writing the query you are probably very familiar with the database schema it is easy to pull everything together. When you come back to that code after a while it may be difficult to remember the table source for the columns, and that knowledge is often necessary.
So, do yourself a favor and fully qualify your column names with the query source used to return them. It will save your grief, and educate others regarding your intentions.
That’s all for tonight. Share your query writing tips by email to btaylor@sswug.org, or leave your comments here to further the conversation.
Cheers,
Ben