Write T-SQL that returns the right answer

Three failures. The silent one first, because nothing raises it: the statement succeeds, the row count looks plausible, the answer is wrong. Then PostgreSQL habit, which at least fails loudly. Then overcorrection, hand-rolling what the engine now does natively.

Checked against Microsoft Learn and sqlcmd 1.10.0, 2026-09-03.

Wrong answers that raise no error

Nothing equals NULL, and nothing is unequal to it either. ANSI_NULLS is permanently ON and SET ANSI_NULLS OFF is deprecated, so every comparison against NULL is UNKNOWN, and WHERE keeps only the rows that are TRUE.

CREATE TABLE dbo.orders (id INT, status NVARCHAR(20) NULL);
INSERT INTO dbo.orders VALUES (1, N'shipped'), (2, N'held'), (3, NULL);