design-azure-sql-schema

# Design a schema Azure SQL Database will not make you rebuild These are the schema decisions Azure SQL Database punishes **later**, not at `CREATE TABLE`. Normalisation and naming are not here: they are the same everywhere. Everything below was measured on 2026-08-28 against a live engine where `SERVERPROPERTY('EngineEdition')` returns `5` and `Edition` is `SQL Azure`. Open [the measured statements](references/key-bytes-collation-and-identity-gaps.md) when a number below disagrees with your engine, and before changing one. | | | |---|---| | The DDL | succeeds, sometimes with a warning nobody reads | | The migration | succeeds | | The failure | arrives on a row, a plan or a failover, weeks later, in production | **A syntax error costs minutes. Each item here costs a table rebuild, a collation rebuild or a duplicated key.** ## 1. The index key budget is in bytes, and Unicode spends two per character **1700 bytes for a nonclustered index key, 900 for a clustered one**, and a composite key spends the **sum**. `NVARCHAR(n)` costs `2n` bytes, so: | Position | Widest safe Unicode column | Widest safe UTF-8 column | |---|---|---| | Nonclustered index or unique constraint | `NVARCHAR(850)` | `VARCHAR(1700)` | | Clustered index or clustered primary key | `NVARCHAR(450)` | `VARCHAR(900)` | | Anything not in a key | unbounded | unbounded | `NVARCHAR(450)` is the only length safe in **every** index position, which is why EF Core narrows an indexed string to it. Over-shooting is a **warning**, message 1945 at severity 10. The index exists, the migration reports success, short values insert fine: ```text Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'ux_acct_email' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail. ``` The failure waits for the first long row, and this one is an error: ```text Msg 1946, Level 16, State 3 Operation failed. The index entry of length 1800 bytes for the index 'ux_acct_email' exceeds the maximum length of 1700 bytes for nonclustered indexes. ``` **Consequence of being wrong: a write path that has worked for months starts rejecting exactly the rows that matter,** and the fix is an `ALTER COLUMN` plus an index rebuild on a live table. - **`INCLUDE` columns do not count against the budget.** A covering index can include `NVARCHAR(MAX)`. - **A foreign key column must match the parent's length and collation exactly**, `Msg 1753` and `Msg 1757`, so an oversized parent key propagates to every child. - `NVARCHAR(MAX)`, `json` and `vector` cannot be key columns at all: `Msg 1919`, `Msg 1978`. ## 2. Collation is chosen at CREATE DATABASE, and it decides two things A new database with no collation stated gets `SQL_Latin1_General_CP1_CI_AS`. `CI` means identifiers are not unique the way the design assumes: ```sql CREATE TABLE dbo.tokens (t NVARCHAR(64) NOT NULL PRIMARY KEY); INSERT INTO dbo.tokens (t) VALUES (N'aB1x'); INSERT INTO dbo.tokens (t) VALUES (N'Ab1X'); -- Msg 2627, Violation of PRIMARY KEY constraint. The duplicate key value is (Ab1X). ``` **Consequence of being wrong: any column holding a case-significant token, API key, slug or short link code collides at roughly the rate its alphabet implies**, as a user-visible insert failure. The fix is per column: ```sql ALTER TABLE dbo.tokens ALTER COLUMN t NVARCHAR(64) COLLATE Latin1_General_CS_AS NOT NULL; ``` **State the collation in `CREATE DATABASE`, or accept the default deliberately.** There is no server collation to inherit, and `ALTER DATABASE ... COLLATE` afterwards re-collates nothing that already exists: it succeeds, changes only what is created next, and leaves you joining across a collation conflict, `Msg 468`. The second half of the default's cost is section 3. ## 3. The implicit conversion that costs the seek The common drivers bind a string parameter as Unicode by default. Under the **default** collation, comparing that parameter to a `VARCHAR` column converts the **column**, and a converted column cannot be sought. Measured over 20,000 rows with a unique index on `email`: | Column | Collation | Parameter | Plan | Logical reads | |---|---|---|---|---| | `VARCHAR(320)` | `SQL_Latin1_General_CP1_CI_AS` (the default) | `NVARCHAR` | **Index Scan** | **80** | | `VARCHAR(320)` | `Latin1_General_CI_AS` | `NVARCHAR` | Index Seek | 2 | | `VARCHAR(850)` | `Latin1_General_100_CI_AS_SC_UTF8` | `NVARCHAR` | Index Seek | 2 | | `NVARCHAR(320)` | any | `NVARCHAR` | Index Seek | 2 | | `VARCHAR(320)` | the default | `VARCHAR` | Index Seek | 2 | ```text |--Index Scan(OBJECT:([dbo].[users_v].[ux_v]), WHERE:(CONVERT_IMPLICIT(nvarchar(320),[dbo].[users_v].[email],0)=[@p])) ``` Row two seeks because a Windows collation lets the optimizer build a range with `GetRangeThroughConvert`. **A SQL collation cannot, and the default is a SQL collation.** **Consequence of being wrong: every lookup on that column reads the whole index instead of three pages, forever, with no error and no warning.** Two designs avoid it, and one has to be chosen up front: 1. **`NVARCHAR` for anything a driver will bind a string to.** Correct, and it costs key bytes. 2. **A UTF-8 collation on `VARCHAR`** when the data is ASCII-dominant and the key is tight. `VARCHAR(1700)` then fits a nonclustered key, and the seek survives because UTF-8 collations are Windows collations. The cost is that `VARCHAR(n)` counts **bytes**. Do not mix. `VARCHAR` under the default collation plus an application binding Unicode is row one. ## 4. Identity gaps are routine here, and the remembered fix does not exist Measured across one engine restart, which is what a planned failover looks like from inside: | Column | Last value before | First value after | Gap | |---|---|---|---| | `INT IDENTITY` | 3 | **1002** | 999 | | `BIGINT IDENTITY` | 3 | **10002** | 9999 | | `SEQUENCE`, default cache | 2 | **51** | 49 | | `SEQUENCE ... NO CACHE` | 2 | 3 | none | | `INT IDENTITY` with `IDENTITY_CACHE = OFF` | 3 | **4** | none | **Consequence of being wrong: any number a human reads or a regulator counts, an invoice number, a ticket number, an order reference, develops thousand-wide holes, unrecoverably.** Nothing errors. ```sql -- The remembered remedy from a non-PaaS engine. Refused here. DBCC TRACEON (272, -1); -- Msg 40518, DBCC command 'traceon' is not supported -- The lever that works. It survives a restart and costs a log write per value. ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF; -- A human-facing number gets its own sequence, and START WITH is not optional: -- omitted, the first value is -9223372036854775808. CREATE SEQUENCE dbo.invoice_no AS BIGINT START WITH 1 INCREMENT BY 1 NO CACHE; ``` A surrogate key may be `IDENTITY`: gaps in a key nobody reads are free, and the cache is why inserts are fast. A number a person reads is not a surrogate key. ## 5. A unique index accepts exactly one NULL This one arrives from PostgreSQL habit, where every NULL is distinct. ```sql CREATE UNIQUE INDEX ux_code ON dbo.accounts (code); INSERT INTO dbo.accounts (code) VALUES (NULL); -- 1 row INSERT INTO dbo.accounts (code) VALUES (NULL); -- Msg 2601, Cannot insert duplicate key row ... The duplicate key value is (<NULL>). -- Msg 2627 instead, where the constraint is a unique constraint rather than an index. -- The fix, verified to accept many NULL rows: DROP INDEX ux_code ON dbo.accounts; CREATE UNIQUE INDEX ux_code ON dbo.accounts (code) WHERE code IS NOT NULL; ``` **Consequence of being wrong: an optional unique field, an external account id, a nullable slug, works for the first row that leaves it empty and rejects the second.** A model with two or three optional unique fields is unusable here and correct on other engines. The session creating a filtered index, and every session writing to the table, needs `QUOTED_IDENTIFIER ON`, else `Msg 1934`. Drivers set it; a command line client may not. ## 6. Where the json and vector types belong Both are storage decisions, not key decisions. | | `json` | `vector(n)` | |---|---|---| | Index key column | no, `Msg 1978` | no, `Msg 1978` | | Primary key | no | no, `Msg 1919` | | `INCLUDE` column | yes, verified | route to `vector-search-azure-sql` | | Compared with `=` | no, `Msg 402` against `nvarchar` | route to `vector-search-azure-sql` | | Ceiling | 2 GB | 1998 dimensions, `Msg 2717` above it | A `json` column is not a filter. Promote anything a query filters on **every time** into a real column: ```sql ALTER TABLE dbo.doc ADD tenant AS CAST(JSON_VALUE(payload, '$.tenant') AS NVARCHAR(64)) PERSISTED; CREATE INDEX ix_doc_tenant ON dbo.doc (tenant); ``` A `vector` column is a payload beside the key, never part of it. A `vector(1536)` occupies 6152 of the 8060 bytes in a row, so keep the table narrow: put the embedding next to an integer key. ## 7. Two boundaries a design cannot cross here ```sql -- No foreign key reaches another database, even one on the same server. ALTER TABLE dbo.local_child ADD CONSTRAINT fk_x FOREIGN KEY (parent_id) REFERENCES otherdb.dbo.some_parent (id); -- Msg 40515, Reference to database and/or server name in 'otherdb.dbo.some_parent' -- is not supported in this version of SQL Server. -- There is one filegroup, so a partition scheme maps everything to it. SELECT name, type_desc FROM sys.filegroups; -- PRIMARY, and nothing else CREATE PARTITION SCHEME ps AS PARTITION pf ALL TO ([PRIMARY]); ``` A shared-lookup-database design has no referential integrity here. Partitioning buys manageability, never storage separation. ## Check it worked Run the DDL through `sqlcmd` with `-m-1`, because message 1945 is **severity 10** and `-b` alone does not report severity 10 at all, so a migration that emitted it still exits 0: **`-m-1` is an ODBC `sqlcmd` instruction**, meaning the 18.x build from `mssql-tools18` or the Microsoft command line utilities. Measured 2026-09-05, go-sqlcmd 1.10.0, the 1.x 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 the `grep` below finds no message 1945 to report. Query `sys.messages` or run the statement through the ODBC build when the number matters. ```bash export SQLCMDPASSWORD='<password>' sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C -b -m-1 \ -i schema.sql -o schema.out grep -nE 'Warning!|^Msg ' schema.out ``` Expect no output from `grep`. A `Warning!` line naming a key length is message 1945 and predicts `Msg 1946` on a production insert. Then audit the schema the engine actually has, as `schema-audit.sql`: ```sql -- 1. Index keys over budget. Expect zero rows. SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, i.type_desc, SUM(CASE WHEN c.max_length = -1 THEN 0 ELSE c.max_length END) AS key_bytes, CASE WHEN i.type = 1 THEN 900 ELSE 1700 END AS budget FROM sys.indexes AS i JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE i.type IN (1, 2) AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 GROUP BY i.object_id, i.name, i.type, i.type_desc HAVING SUM(CASE WHEN c.max_length = -1 THEN 0 ELSE c.max_length END) > CASE WHEN i.type = 1 THEN 900 ELSE 1700 END; -- 2. Unique indexes over a nullable column with no filter: one NULL row each. Expect zero. SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, c.name AS column_name FROM sys.indexes AS i JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE i.is_unique = 1 AND i.has_filter = 0 AND c.is_nullable = 1; -- 3. VARCHAR or CHAR key columns on a SQL collation: the lost seek. Expect zero rows. SELECT DISTINCT OBJECT_NAME(c.object_id) AS table_name, c.name AS col, c.collation_name FROM sys.columns AS c JOIN sys.index_columns AS ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id AND ic.is_included_column = 0 JOIN sys.types AS t ON t.user_type_id = c.user_type_id WHERE t.name IN ('varchar', 'char') AND c.collation_name LIKE 'SQL[_]%'; -- 4. Sequences left on the type minimum because START WITH was omitted. Expect zero rows. SELECT name, CAST(start_value AS BIGINT) AS start_value FROM sys.sequences WHERE CAST(start_value AS BIGINT) < 0; -- 5. The two settings the design assumed. Read them, do not assume them. SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS db_collation, (SELECT value FROM sys.database_scoped_configurations WHERE name = 'IDENTITY_CACHE') AS identity_cache; ``` ```bash sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C -W \ -i schema-audit.sql -o schema-audit.out ``` Checks 1 to 4 return no rows in a clean schema. Check 5 is the one you read rather than pass: `identity_cache` of `0` means gaps are off, `1` means an `INT IDENTITY` will jump by about a thousand across the next reconfiguration. Last, confirm the seek survived on one real lookup: ```sql SET STATISTICS IO ON; DECLARE @p NVARCHAR(320) = N'u9999@example.com'; SELECT id FROM dbo.users WHERE email = @p; SET STATISTICS IO OFF; ``` Expect single-digit logical reads that do not grow with the table. Reads in the tens over twenty thousand rows is the implicit conversion in section 3, not a cold cache. ## Do not - Do not read message 1945 as informational. It is the only notice given, and severity 10 means neither `sqlcmd -b` nor most drivers raise it. - Do not size a key column in characters. The limit is bytes, and Unicode doubles it. - Do not use `VARCHAR` under the default collation for anything an application looks up by value. - Do not add `LOWER()` or `UPPER()` around a column to fix a collation problem. That costs the index too, and it is `t-sql-correctness`. - Do not reach for trace flag 272. It is rejected with `Msg 40518`. The lever is `IDENTITY_CACHE = OFF`. - Do not put an invoice or ticket number, or anything an auditor reads, on a bare `IDENTITY`. - Do not create a `SEQUENCE` without `START WITH`. - Do not give a model two or three optional unique columns and expect PostgreSQL NULL semantics. - Do not store a value in a `json` document and then filter on it. Promote it. - Do not add a clustered index only because you believe this engine demands one. A heap was created and took rows on 2026-08-28. ## References - Open [the measured statements](references/key-bytes-collation-and-identity-gaps.md) when you need the statement behind a number above, the two query plans, or the reproduction steps. - Read [Maximum capacity specifications](https://learn.microsoft.com/sql/sql-server/maximum-capacity-specifications-for-sql-server) before designing a wide composite key. - Read [Collation and Unicode support](https://learn.microsoft.com/sql/relational-databases/collations/collation-and-unicode-support) before choosing a database collation: SQL against Windows collations, and UTF-8. - Read [json data type](https://learn.microsoft.com/sql/t-sql/data-types/json-data-type) before putting a document in a column, for the index restrictions and the size limits. - Read [Database scoped configurations](https://learn.microsoft.com/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql) before setting `IDENTITY_CACHE`, and [T-SQL differences](https://learn.microsoft.com/azure/azure-sql/database/transact-sql-tsql-differences-sql-server) when a statement is refused with 40510, 40515, 40517 or 40518. - `ef-core-azure-sql` and `sqlalchemy-azure-sql`: how each mapper reaches these lengths and the default it lands on. The other mapper skills are listed in `skill.spec.jsonc`. - `t-sql-correctness` for query-level correctness, `t-sql-json-and-openjson` for querying a document column, `vector-search-azure-sql` for the vector type and its indexing.