# T SQL Correctness

> 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.

- Skill: `microsoft/t-sql-correctness` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx get-skill add microsoft/t-sql-correctness`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/t-sql-correctness/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/t-sql-correctness

---


# 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 <server-name>.database.windows.net,1433 -d <database> -U <user> -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.

