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…
Does SQL Server Support Custom Domains?
If you have spent time with PostgreSQL or Oracle, or read enough of the SQL standard to regret it, you have probably met the CREATE DOMAIN statement. A domain is a named, reusable type with…
IS DISTINCT FROM: Comparing NULLs Without the Headache
It usually shows up in a code review. Someone writes a filter that is supposed to exclude voided rows, it looks completely reasonable, and yet a tester swears that records are going missing. The query…
Self-Contained Transaction Scripts in SQL Server: the @debug_only Pattern
A safer alternative to the commented-out COMMIT habit: a @debug_only flag, SET XACT_ABORT ON, a named transaction, and an XACT_STATE guard that never leaves a transaction open.
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…