--- name: t-sql-correctness description: "Use when writing, porting or reviewing T-SQL for Azure SQL Database, or results look wrong with no error: NULLs, NOT IN, integer division. Has verified behavior common answers miss, e.g. the 'NULL eliminated' warning never reaches sqlcmd." --- # 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. ## Scope - Upserts and `MERGE` are `t-sql-upserts-merge`. - JSON and `OPENJSON` are `t-sql-json-and-openjson`. - Table design is `design-azure-sql-schema`. ## 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`. ```sql CREATE TABLE dbo.orders (id INT, status NVARCHAR(20) NULL); INSERT INTO dbo.orders VALUES (1, N'shipped'), (2, N'held'), (3, NULL); DECLARE @p NVARCHAR(20) = NULL; SELECT COUNT(*) FROM dbo.orders WHERE status = @p; -- 0, not 1 SELECT COUNT(*) FROM dbo.orders WHERE status <> N'shipped'; -- 1, not 2 -- Null-safe equality, true or false and never unknown: SELECT COUNT(*) FROM dbo.orders WHERE status IS NOT DISTINCT FROM @p; -- 1 ``` The second line reaches production: "everything not shipped" drops every row whose status is unknown. **`NOT IN` over a nullable column returns nothing at all.** One `NULL` in the subquery makes the predicate `UNKNOWN` for every candidate row, so the answer is empty rather than short by one. ```sql CREATE TABLE dbo.assigned (customer_id INT NULL); INSERT INTO dbo.assigned VALUES (1), (NULL); CREATE TABLE dbo.customers (id INT NOT NULL); INSERT INTO dbo.customers VALUES (1), (2), (3); SELECT COUNT(*) FROM dbo.customers c WHERE c.id NOT IN (SELECT customer_id FROM dbo.assigned); -- 0 -- NOT EXISTS is the fix, and the habit worth defaulting to: SELECT COUNT(*) FROM dbo.customers c WHERE NOT EXISTS (SELECT 1 FROM dbo.assigned a WHERE a.customer_id = c.id); -- 2 ``` The rewrite is the fix. The `NULL` rows are not the bug, so find out what writes them before you delete any. Add `NOT NULL` only after that writer is fixed and the existing rows are settled. **Aggregates skip nulls, and in practice nobody is told.** The engine does send message 8153, "Null value is eliminated by an aggregate or other SET operation", as an informational message, as Microsoft Learn's `SET ANSI_WARNINGS` page says, on Azure SQL Database and SQL Server 2022 alike, and a .NET SqlClient `InfoMessage` handler receives it. The tools in between drop it: the ODBC `sqlcmd`, even at `-I -m-1`, `mssql-tools18` `sqlcmd` and `pyodbc` print nothing, and an ORM or dashboard that does not subscribe to informational messages shows nothing either. So do not plan on being told. ```sql CREATE TABLE dbo.readings (v INT NULL); INSERT INTO dbo.readings VALUES (10), (NULL), (20); SET ANSI_WARNINGS ON; -- makes no difference here SELECT SUM(v), COUNT(*), COUNT(v), AVG(v) FROM dbo.readings; -- 30, 3, 2, 15 ``` `AVG` divides by 2, not 3, and `COUNT(v)` disagrees with `COUNT(*)` for the same reason. If the intent was 10, write `AVG(ISNULL(v, 0))` and mean it. **Integer division truncates, and `ISNULL` truncates its own replacement.** `/` returns the higher-precedence operand's type, and two integers give an integer. `ISNULL` returns the type of its **first** argument and converts the second into it; `COALESCE` returns the highest-precedence type of all of them. ```sql SELECT 7 / 2, CAST(7.0 / 2 AS DECIMAL(4,1)); -- 3 and 3.5 DECLARE @short NVARCHAR(3) = NULL; SELECT ISNULL(@short, N'abcdef'), COALESCE(@short, N'abcdef'); -- abc and abcdef ``` `ISNULL` also reports its result as not nullable where `COALESCE` reports nullable, so a computed column that must come out `NOT NULL` needs `ISNULL`. **A string with no length is one character, except in a cast, where it is thirty.** Assigning to an undersized variable truncates in silence; only an undersized column refuses. ```sql DECLARE @v NVARCHAR = N'abcdef'; SELECT @v, LEN(CAST(REPLICATE(CAST(N'a' AS NVARCHAR(MAX)), 40) AS NVARCHAR)); -- a and 30 ``` Always give a length. The inner cast is there because `REPLICATE` otherwise returns `nvarchar(4000)`. ## Session settings change what a statement means `QUOTED_IDENTIFIER` is decided at parse time, so the same text is two different statements: ```sql SET QUOTED_IDENTIFIER OFF; SELECT "not a column"; -- returns the string, one row SET QUOTED_IDENTIFIER ON; SELECT "not a column"; -- Msg 207, invalid column name ``` `ON` is the default and the ODBC and OLE DB drivers set it on connect, so application code sees `ON`. The `sqlcmd` in the Azure SQL Database container image leaves it **OFF**, so a script behaves one way in a container shell and another way run by the app. Pass `-I` to settle it. ## PostgreSQL habit, translated | It will write | It must write | Because | |---|---|---| | `LIMIT 10 OFFSET 20` | `ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY` | `OFFSET` and `FETCH` are clauses **of** `ORDER BY`; `FETCH` without `OFFSET` is invalid | | `RETURNING id` | `OUTPUT INSERTED.id` | Read the trigger rule below before shipping it | | `id SERIAL PRIMARY KEY` | `id INT IDENTITY(1,1) PRIMARY KEY` | Or a `SEQUENCE` when the generator is shared | | `is_active = true`, `WHERE is_active` | `is_active = 1` | No Boolean type. `bit` is an integer, and a value rather than a predicate | | `NOW()` | `SYSDATETIME()`, or `SYSUTCDATETIME()` | `CURRENT_TIMESTAMP` also works and is ANSI | | `name ILIKE 'ana%'` | `name LIKE 'ana%'` | The default collation `SQL_Latin1_General_CP1_CI_AS` is already case-insensitive | | `WHERE name = "ana"` | `WHERE name = 'ana'` | Escape a quote by doubling it, `'it''s'`, and prefix Unicode with `N` | | `bio TEXT` | `bio NVARCHAR(MAX)` | `text` and `ntext` are deprecated and barred from several operators | | `ON CONFLICT DO UPDATE` | See `t-sql-upserts-merge` | That skill owns upserts, including when not to use `MERGE` | | `USE otherdb;` | Open a new connection | Unsupported, along with cross-database three and four part names | `USE` fails with `Msg 40508` in the cloud and in a user-database session on the local Azure SQL Database container: open a connection to the other database instead. Open [references/postgres-to-tsql.md](references/postgres-to-tsql.md) before porting a schema or a query file, and when a statement crosses a database boundary. `TOP` and `OFFSET FETCH` cannot be combined in one query expression, and a stable page needs a **unique** sort key: `ORDER BY placed_at DESC, order_id DESC`. A bare `OUTPUT` fails with `Msg 334` when the target has an enabled trigger for that action, so capture into a table variable: ```sql CREATE TABLE dbo.new_orders (order_id INT IDENTITY PRIMARY KEY, total DECIMAL(9,2)); DECLARE @new TABLE (order_id INT); INSERT INTO dbo.new_orders (total) OUTPUT INSERTED.order_id INTO @new VALUES (19.99); SELECT order_id FROM @new; ``` The `INTO` target has its own limits: no enabled triggers, neither side of a foreign key (`Msg 332`), no enabled `CHECK` constraints or rules (`Msg 333`), and no guaranteed row order. Prefer `OUTPUT` to `SCOPE_IDENTITY()`: it returns every row of a multi-row insert, and works on `UPDATE`, `DELETE` and `MERGE`. ## Do not avoid these. They work | Available | Note | |---|---| | `a \|\| b` and `\|\|=`, `UNISTR` | Generally available July 2025. Unlike `CONCAT`, `\|\|` yields `NULL` if any input is `NULL` | | Regular expression functions | Generally available November 2025. Check the database compatibility level and current function-specific limits before use | | `STRING_AGG(x, ',') WITHIN GROUP (ORDER BY x)` | Any compatibility level, but returns `nvarchar(4000)` for `nvarchar(1..4000)` input, so cast to `max` or lose the tail | | `TRIM(BOTH '.' FROM s)` | The positional keywords need a recent compatibility level; below it they are a **parse** error | | `GREATEST(a, b, c)`, `LEAST(...)` | Nulls ignored unless every argument is null | | `a IS NOT DISTINCT FROM b` | Null-safe equality, as above | A migrated database can sit at an old compatibility level, where the failure looks exactly like the function not existing: `Msg 102`, `Msg 195` or `Msg 208` at level 150. Check the level before believing the error. Treat raising the compatibility level as a separate migration change that requires workload testing. ## Check it worked Save this as `check-correctness.sql`. The exit code proves nothing here: every statement succeeds. ```sql -- 1. The session your script was actually parsed and run under. SELECT IIF((256 & @@OPTIONS) = 256, 'ON', 'OFF') AS quoted_identifier, IIF((32 & @@OPTIONS) = 32, 'ON', 'OFF') AS ansi_nulls, IIF((8 & @@OPTIONS) = 8, 'ON', 'OFF') AS ansi_warnings; -- 2. String columns declared with no length: they hold one character. SELECT OBJECT_NAME(object_id) AS t, name AS c FROM sys.columns WHERE system_type_id IN (231, 239, 167, 175) AND max_length <= 2; ``` ```bash sqlcmd -S .database.windows.net,1433 -d -U -C -I -b -m-1 \ -i check-correctness.sql -o check-correctness.out ``` Expected: check 1 returns `ON ON ON`, check 2 no rows. An `OFF` means your script was parsed under different rules from the ones the application connects with. `-m-1` is there because `-b` sets a non-zero exit only at severity 11 and above, so any severity 10 message leaves a script reporting success. **`-m-1` is an ODBC `sqlcmd` instruction**, meaning the 18.x build from `mssql-tools18` or the Microsoft command line utilities. go-sqlcmd 1.x, the build `brew install sqlcmd` and `winget install sqlcmd` install, prints no `Msg` header on a severity 10 message at any `-m` value, so on that build a quiet output file is not evidence that the engine stayed quiet. Query `sys.messages` or use the ODBC build when the number matters. ## Do not - Do not compare with `= NULL` or `<> NULL`, and do not "fix" it by turning `ANSI_NULLS` off. That setting is deprecated and permanently `ON`. - Do not use `NOT IN` against a subquery over a nullable column. Use `NOT EXISTS`. - Do not swap `ISNULL` for `COALESCE` as a cosmetic edit. The return type differs, so the value can change. - Do not translate `ILIKE` into `LOWER(col) = LOWER(@v)`. It fixes nothing on a case-insensitive collation and costs the index seek. - Do not size, collate or index columns here. `design-azure-sql-schema` owns that, including why a `json` column compared with `=` fails with `Msg 402`. - Do not expand a correctness fix into unrelated error-handling (`TRY`, `CATCH`, `XACT_ABORT`) or trigger design; treat those as separate changes with their own tests. ## References - [references/postgres-to-tsql.md](references/postgres-to-tsql.md): open it when porting a schema or a query file, when a statement crosses a database boundary, or to find the Microsoft Learn page behind a row in the table above.