Coding Standards
(T-SQL Tuesday #151)

I'll go with a rapid-fire strategy, just a long list of things to do and avoid with only a brief explanation. Some of these I've already covered in my rant.

Don't do this

I'll start with the don't because the risk of doing something terrible usually outweighs doing something really well.

No scalar functions

Lots of people underestimate just how lousy scalar functions are.

• You force the SQL to process row by agonizing row (RBAR).
• It's invisible to SET STATISTICS IO, TIME ON, so you might not even know how bad it is.
• It prevents parallelism, even if the function is buried in a column of a view you aren't even referencing.
• It can be hidden in Computed Columns or Check Constraints.

Even though the topic is already covered by many, I might blog about it later.

No Merge

I'll leave Aaron Bertrand wrote about why you should avoid it.

No magic constants

WHERE p.ProductType <> 4

What is 4? Just set a variable (constant) from a lookup table. Or write a comment with an explanation.
It's the least you can do.

No meaningless wrappers

I mean brackets and parentheses:

WHERE
(
  ([p].[ProductType] <> 4)
  AND ([p].[CreateDate] >= '2022-06-12 00:00:00.000')
)

could just be this:

WHERE
  p.ProductType <> 4
  AND p.CreateDate >= '2022-06-12 00:00:00.000'

There is no need for this extra eye strain. I only use brackets when the identifier turns blue. Parentheses should be added to help readability. Also, when the order of operations might not be clear, like when AND plus OR is combined.

No business logic/validation in Triggers

Stay away from the Triggers. I have only two use cases for them:

• Scaffolding during refactoring
  • Sync two copies of the same table
  • INSTEAD OF Trigger on a view
• Enforcing audit columns (LastModificationDate, ModifiedBy)
  • Even though now we can use Temporal tables

No capes!

Sorry, that's just my favourite Incredibles quote. Not sure how that got here.

No ordinals

Apart from a quick ad-hoc query, never use this

ORDER BY 1, 3 DESC, 2

It's very brittle. Someone will come and change the select order and screw up the logic.
It's terrible for readability.

No old JOIN syntax

Even though I cannot remember the last time I saw it, don't use the ANSI-89 JOIN syntax.

SELECT *
FROM dbo.Customer AS c, dbo.OrderHeader AS oh
WHERE
  oh.CustomerId = c.CustomerId

There is just no need to use this.
If you forget the join condition in the WHERE clause, you have a Cartesian product (row count from c × row count from oh).

Always* do this

On the other hand, I usually recommend these (asterisk applies).

Alias everything

Especially table names. I prefer to use initials.

Nullability is specified

When declaring table variables or temp tables, always specify if the column can be NULL. It helps to set the expectations about the data.

All constraints are named

Some schema compare tools are smart enough to disregard those. But if you script out two objects and compare them with a diff tool, you will find many differences if the constraints are not explicitly named.

Scripts should be idempotent

I had to search the term definition the first time around. It means that the script can be run many times but does that thing you wanted only once.

Unless, of course, you rely on the script to break if something is amiss.

Only regular identifiers

I'm not too fond of brackets, so don't force me to use them.
You don't have to type out brackets when using only regular identifiers.

As few hints as possible

They say: "Change is the only constant", but it's not true. So are the hints. Once they're used, they usually stay. It's a slippery slope telling SQL how it should behave - unless you are Paul White (SQL Kiwi) .

XACT_ABORT everywhere

SQL Server is not very consistent in handling transactions, so it needs all the help it can get. Michael J Swart wrote about why you shouldn't abandon your transactions.

Always be deterministic

If you ever wondered why the same query hands back rows in a different order from one run to the next, determinism (or the lack of it) is usually why. So I add a surrogate PK to the ORDER BY clause whenever the order has to be stable.

The TOP clause has parentheses

A little-known fact is that the TOP operator accepts an expression. But it needs to be wrapped in parentheses like so

[
  TOP (expression) [PERCENT]
  [ WITH TIES ]
]

Everything is schema-qualified

Apart from the default schema issues, it helps differentiate real tables from table expressions.
But it can even lead to Compile locks blocking

One condition per line

It makes it easier to read and move conditions around.

Do you agree with my standards? Let me know and thank you for reading.

Thank you for reading