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…
The SET Succeeds and the Catalog Read Succeeds, Then Msg 3951
Snapshot isolation is the answer to most of the problems WITH (NOLOCK) gets used for, which I covered in . Readers stop waiting for writers, and unlike read uncommitted, everything they read was actually committed….
NOLOCK Returned a Balance That Never Existed
WITH (NOLOCK) gets added to queries for one reason: something was slow, or something was blocked, and the hint made the query run. It does make blocking stop. What it gives up in exchange is…
A Nested COMMIT Does Not Commit Anything
A procedure that opens its own transaction is fine on its own. Call it from another procedure that already opened one and the arithmetic stops matching your intent. SQL Server does not have nested transactions….
COUNT Under the Hood
COUNT() and COUNT_BIG() do the same thing: they return the total number of rows in your result set, or the number of rows-per-group with GROUP BY. Only COUNT_BIG() works when there are more than 2,147,483,647…
COALESCE in a WHERE Clause Costs You the Row Estimate
Optional filter parameters are everywhere in reporting procedures. Pass a value and you want that value; pass NULL and you want everything. This post is part of the series. The contents page lists every part…