It’s not quite a Best Practice, but it’s something that I see as very important. It makes the difference between someone who might be quite good at T-SQL, and someone who can go past the rest and become one of those people who get asked to solve other people’s T-SQL problems.
It’s easy – you read the plans.
You see, the plans explain to you what you’re actually doing, instead of just following the standard formula to get it done.
It’s the same in many parts of life. If you’re going to be a mechanic, it helps to understand how an engine works. If you’re a pilot, you should understand the principles of flight.
And so it is with T-SQL. We write a query, and the Query Optimizer pulls it apart and translates it into an execution plan. It’s this plan that runs, not our query. Unfortunately, the nuances of this translation demonstrate all of us ignorant. There may be people who understand it better than most, such as some of them people who work on the Query Optimizer at Microsoft.
Grasping a handle on the main ideas is definitely worthwhile though. If you can understand the ways that Physical Joins work, and the things that influence index choices, you will naturally write better queries. You may still have trouble working out the logic that produces the correct result, but trouble-shooting will be much easier and performance gains will surely follow.