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. There are two common ways to write it, and I have carried a…
One NULL in the List and NOT IN Returns Nothing
NOT IN and NOT EXISTS read like the same thing. Find the rows that are not in that other set. They agree until the other set contains a NULL, at which point NOT IN returns…
ISNULL Truncates Your Replacement Value; COALESCE Doesn’t
ISNULL and COALESCE look like the same function wearing different names. Both take a value, both give you something else when that value is NULL. They differ in how they decide the type of what…
Double-Hyphen Comments Can Comment Out Your WHERE Clause
-- and /* */ both comment out T-SQL. The difference is where they stop. /* */ stops where you close it. -- runs to the end of the line,[2] and if there is no end…