Advice about T-SQL for beginners

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.


Advice, wasted on the young

In 1997, Baz Lurhmann released a song called "Wear Sunscreen", based on text written by Mary Schmich (although I had to look that up). It was a hypothetical speech that would be given to a graduating class and while it contained a bunch of good and interesting advice, the title of Schmich's essay was "Advice, like youth, probably just wasted on the young".

This month, the T-SQL Tuesday writing challenge is to comment on what advice we'd give to a younger version of ourselves – invited by Camila Henrique – so it's probably worth highlighting that I probably would've ignored it. I'm sure I wasn't good at taking advice. I'm not sure I'm any better now, despite not being young any more.

Advice to our younger selves tends to imply regret. Things that we'd like to change. Advice to make different decisions or to establish different habits. Advice to cherish things before we lose them. I suspect Schmich suffered from things prevented by sunscreen, or lost friends and relatives. Or maybe she just wanted to have something that would be a good hook for a callback at the end, for the line "But trust me on the sunscreen".

And the advice I would give depends massively on what age me I'm giving the advice to. There are things that I would tell me at 4 or 5, that are different to what I would tell myself at age 9 a year before my dad died. If I'm talking to myself during my high school years, I would give difference advice compared to what I would tell myself as my kids were growing and going through their own stuff. Maybe I'd warn myself about failed projects, and lost data, and I'd say yes to that thing in 1999.

I would want to say that things will work out. Except that they don't. When people die, it doesn't feel like things are working out. When friendships break down, they're often not going to get resolved. When employees quit or clients leave, well I'm sure you get the idea.

But time keeps on going. The things in the past will remain in the past. We can't always get them back. Things will hurt. Life will suck. But there will also be times so full of joy that all the shadows disappear for a moment.

I'd tell my former self that. That there will always be good times in the future.

In fact, I'll tell current-self that. And maybe my future-self too.