Why I Keep a T-SQL Style Guide, and What Testing It Cost Me

I keep a file of T-SQL conventions. Square brackets on identifiers, COALESCE over ISNULL, block comments, NOT EXISTS over NOT IN, and a dozen more.

It is not there because consistent code is prettier. It is there so I do not re-decide the same question every time it comes up.

Decisions, not preferences

Most style rules do not matter much in isolation. Whether you write COUNT(1) or COUNT_BIG(1) in a query that counts eleven rows is not a real question.

What matters is that the decision was made once, by someone who looked into it, and does not have to be made again under time pressure by someone who has not. That is the whole function of the guide.

The rules that earn their place are the ones where the wrong choice fails in a way you would not catch: a result that is wrong rather than absent, a query that works until the data changes, a habit that is harmless at small scale and expensive at large scale. Every post in this series covers one of those.

The ones that just make code look uniform matter less, and I hold them more loosely.

Rules that fail loudly do not need to be rules

A convention against something that raises an error immediately is close to worthless. The compiler or the first test run enforces it for you.

The rules worth writing down are the ones covering things that succeed and give you the wrong answer:

ISNULL returning a value truncated to fit its first argument, with no warning. NOT IN returning zero rows because one NULL appeared in a subquery. A -- comment consuming a WHERE clause when generated SQL is concatenated without a line break. NOLOCK returning a balance that was rolled back seconds later.

None of those raise anything. All four return a plausible answer. That is the category the guide exists for.

What testing it cost me

Writing this series meant proving every rule against a real instance rather than restating what the file said. Two of them did not survive the process intact.

The COUNT_BIG rule. My note said COUNT computes in int and overflows. It does not. The engine counts in bigint regardless, and COUNT adds a conversion at the end that narrows the result back down. The rule holds, and the mechanism I had written down was backwards. That is COUNT Under the Hood.

The optional parameter rule. My note said COALESCE(@p, col) blocks index seeks. It does not. With OPTION (RECOMPILE) both forms seek, and without it both scan. The actual difference is that COALESCE hides the predicate from the cardinality estimator, which then guesses 10% of the table. The rule holds, and the reason was wrong. That is COALESCE in a WHERE Clause Costs You the Row Estimate.

Both had been in the file for years. Both had been repeated in code review comments. Neither had been checked.

A rule you cannot explain correctly still produces the right code most of the time, which is why the error survives. It fails when someone applies the stated reason to a case it does not cover, or reasonably pushes back and you defend it with something that is not true.

Testing changed the rules I thought were solid

The pattern I did not expect was how often a small test produced a misleading result before a realistic one produced the real answer.

Comparing NOT IN to NOT EXISTS on empty tables gave identical plans for both, which would have supported a claim that there is no performance difference. With 100,000 rows the difference was roughly twentyfold.

Testing optional parameters with a local variable rather than a procedure parameter measured the wrong thing, because local variables are not sniffed.

Checking snapshot isolation by querying a catalog view reported success where a user table would have failed with Msg 3951.

In each case the quick test agreed with whatever I already believed. That is the failure mode to watch for. A test small enough to run casually is often too small to distinguish the answers.

What belongs in a guide

Three things earn a place, in my experience:

The rule, stated as an instruction. Not a discussion, not a list of options. Someone reading it at 11pm needs to know what to type.

The reason, stated accurately. This is the part that decays. It is also the part that lets somebody recognise the case the rule does not cover.

The evidence, or an honest note that there is none. A rule inherited from a blog post read a decade ago is not worthless, but it should say so, because that is the one to check first when it is challenged.

The third item is the one I had been skipping, and it is why two entries in my own file were wrong.

Where I would start

If you do not keep one, the useful starting point is not a list of formatting preferences. It is the last three bugs you shipped that returned a wrong answer rather than an error. Write down what would have prevented each, and you have three rules that will prevent more damage than thirty about indentation.

Add the reason. Then, at some point, check the reason.

This series covers the rules from mine that I found worth defending in public, each with the test that supports it. Where a test contradicted me, the post says so, because a style guide that records only the entries that held up is one I would trust less myself.

Which of your own conventions would survive being tested? Bluesky or LinkedIn.