T-SQL Tuesday #149 – T-SQL Advice You’d Give To Your Younger Self

This month’s invitation from Camila Henrique asks us for advice we’d give to our younger selves in our T-SQL journey. Coming from the SQL Server perspective there’s a huge variety of areas within the stack where you might want to provide some advice but I’m going to focus on the scripting and querying as that’s likely relevant to more folks.

The advice I’d give is just this: Keep it simple.

We’ve got a lot of tools in our belt when it comes to writing our scripts so when I say simple I’m not referring to throwing all of those out of the window. There’s a time and a place for each of them but that doesn’t mean that every solution should be a single statement query spread over 150 lines where you’ve layered a function inside a recursive CTE which calls some CLR code. Aim for something simple and if it isn’t performing as we need then evaluation the options and make specific changes based on the result you’re after.

Be consistent

Every solution will naturally be different in some way, but if you’re consistent in the way you’re approaching each task then the familiarity will bring its own benefits. Sometimes that consistency may come from your own style and the way you design your scripts or write you code. Other times that consistency might be something within your team and the way your produce your deliverables in consistent ways to make supporting them easier in the future.

Having a set of standards as your baseline gives you a springboard for anything new. Things might change as the solution develops and although we might need to tailor based on its own nuances, if we start consistently then we won’t need to be reinventing the wheel. Some examples of the types of things which spring to mind personally:

• Having foreign key constraints for all related tables
• Define constraints for natural keys within the data
• Create schemas to separate staging, fact and dimension tables
• Alias tables within queries using meaningful names
• Using temporary tables to break queries into smaller segments

Learn from others

Working with technology one of the most exciting parts of our journeys are learning new skills or tactics for dealing with the challenges we face. The elements we learn tend to fall into one of two categories – the shiny new features which are exciting to use, and then there’s the basics which we’ve heard time and time again.

In this context its those basics which I feel are most relevant. You might have folks telling you to use a clustered identity column on tables, saying that you should make sure your text fields are sized appropriately rather than VARCHAR(MAX) and that you shouldn’t be doing SELECT * when querying tables.

I’m not saying that you should do everything you’re told or read about on the internet – that would probably be dangerous – but when there are things you see consistently from the folks who have been in your team a little longer or are well regarded in the industry – there’s usually a reason for it. Its likely that adopting some of these can avoid pain points later and you might understand these further as the systems grow and processes scale.

As I said you don’t want to follow everything blindly so if these views differ to yours its a good starting point to jump in a little deeper so you can compare and contrast the approaches to make more informed decisions and adjust your baseline if needed (remember to be consistent either way!).

Be kind to those who come afterwards

When we’re working in code and shifting our priorities and focus across different projects it can be quite easy to misplace knowledge over time. Sometimes this may come from a change in SMEs within the team, and other times its some legacy scripts which have existed ‘forever’ as far as everyone knows. Either way, its not fun having to decipher code before you can work on it.

Code is thankfully somewhat self explanatory and if you’re scripting a SELECT to go into a procedure for a report it may be straight forward. When that’s not the case however and you’re implementing business logic or making bespoke targeted changes, you want to do it in a way that anyone who follows in your steps can understand what was done and why.

We’ve already discussed taking consistent approaches within the team and trying to follow some general best practices, so here it’s about the traceability – what was done and why. The easiest method for this is adding some comments into your scripts. They aren’t needed for every statement, but for the areas you want to emphasise with the specific logic or where you’ve taken a slightly more complex solution to a challenge they can pay dividends. Below are a few thoughts on how to help with these:

• Comment with your initials, date of the change, ticket number, etc. so folks know who to get in touch with and have points of reference to follow up further
• If its complex code or there is logic there then describe what and why its been done and possibly even why you’ve done it that way
• Make reference to any specific documents or colleagues within the business who might have provided resources to support the requirements such as links to process or legislation changes
• If using a source control / issue tracking system then make sure the code is checked in and referenced in the ticket to tie everything together

As a bonus, if someone can better read and understand your code there’s less chance it’s coming back to you if it needs reviewing down the line – you’re doing your future self a favour. You’re welcome.

Wrap up

I’ll admit that my views might not be the most radical or life changing but I’ve fallen fowl of overcomplicating things enough times to realise that sometimes its worth taking a step back and simplifying the situation to understand where the real issues are instead of over-engineering a solution for what I thought the issues were.

Maybe that query needs to be 150 lines with the function inside a recursive CTE which calls some CLR code. That said, maybe it only needs a couple of temp tables, a covering index and a couple of comments and we can move along.