Following on from my last post… what advice would I give about T-SQL to my younger-self?
Well, for that I’m going back a long way. To when I had learned about queries, but still had some way to go.
It’s the same advice I give to everyone who’s learning to write T-SQL, even the most basic of beginners. And that is to understand that the queries you write get turned into execution plans, and it’s them that actually run.
Your query is only part of the story. It also depends on everything else the system knows about the environment your query is running in. It looks at the indexes (even ones that aren’t being used for your query), statistics, the parameters you are passing in, whether or not the query has been run before… you get the picture.
So when someone says that their query isn’t running the way they’d like – it’s quite often because of other factors. What might look like an excellent query might be being spoiled because of an atypical parameter that was used to create a plan that’s now in cache. Or that index that would be amazing might have been disabled. Or there might be a unique index that has been created, so that the system thinks there’s a different way to run the query, or the system might think there are no rows in the table for today.
Know about the plan. Seek to understand it. Get your head around the different ways that your query could run, and the things that will cause the Query Optimizer to choose a different plan.
It took me a few years to work this one out. These days I teach it to everyone who gets into T-SQL. Right from day one, I teach them that even the simplest of queries could be turned into an unexpected plan.
@robfarley.com@bluesky (previously @rob_farley@twitter)
