Breaking the rules

, ,

January 2022's T-SQL Tuesday is hosted by Andy Yun (b|t) – thanks Andy! This month, Andy's asking about learning that changes your opinion.

In my case, it's not one specific example, but several, that changed the way I think about rules.

What rules?

When I started out writing T-SQL, I learned rules – things I should do, things to avoid – from other, more experienced developers. For example, when writing stored procedures:

  • don't use dynamic SQL
  • don't set variables wih SELECT
  • never use WITH RECOMPILE

There are often great reasons for these. Dynamic SQL can leave you prone to SQL injection, SELECTing into variables can have unexpected effects, and RECOMPILE can bring an OLTP system to its knees.

When to break them

Working as a data engineer, I break these three rules regularly 😊.

Dynamic SQL is great for generating boilerplate ETL code. Combining it with metadata allows me to implement thousands of lines of transformation code quickly for standard patterns. I've always found SELECTing into variables convenient here (particularly before STRING_AGG) because it allows long lists of column names to be assembled into code, for example to create a table, or to upsert data into it.

RECOMPILE will hurt if a stored procedure has to run thousands of times a day in under 30ms – but if it's a long-running ETL routine that processes large and changing amounts of data, perhaps only a few times a day, then the cost of recompiling it before execution might be worthwhile.

What I learned

Rules are shorthand. When you learn how to do anything new, it's hard to understand or remember all the reasons for doing it in a particular way. Rules help you make sense of that, by giving you fast, concrete answers that get you moving in the right direction – but they're not the whole picture.

As Phil Factor says, a code smell – or a broken rule – is “a hint of possible bad practice to a pragmatist, but a sure sign of bad practice to a purist”. Understanding why a rule exists – rather than just following it blindly – enables you to be pragmatic about breaking it when doing so is useful.