CREATE INDEX can finish while leaving a warning about the next long value. Understanding index key size means checking the stored bytes and future values, not only whether CREATE INDEX finished.
Match the Index Key Size Limit to the Index Type
For ordinary rowstore indexes in SQL Server 2016 and later, a clustered key has a 900-byte limit and a nonclustered key has a 1,700-byte limit. Earlier versions used 900 bytes for both. Specialized index types have different rules, so identify the actual structure before applying these numbers.
The limit applies to the combined key, not separately to each column. Two individually acceptable string columns can exceed it together. The stored byte length matters. An nvarchar definition uses two bytes per declared character position, while varchar storage depends on the relevant encoding and values. Do not treat every string length as an interchangeable character count.
I review the complete key and the expected value contract before adding a wide index. A design that passes today's short values can fail when the application submits a longer valid value tomorrow. A warning at creation time is a useful early message, not a certificate that future writes are safe.
See an Index Key Size Failure Arrive Later
A variable-length key definition can exceed the limit while every existing value stays within it. SQL Server creates the index and warns about that possibility. The following temporary-table example uses a nonclustered key and deliberately attempts an oversized value afterward.
CREATE TABLE #WideKeyLab
(
ItemID int NOT NULL PRIMARY KEY,
LookupValue varchar(2000) NOT NULL
);
INSERT #WideKeyLab(ItemID,LookupValue) VALUES(1,'Short input');
CREATE NONCLUSTERED INDEX IX_WideKeyLab_Lookup
ON #WideKeyLab(LookupValue);
BEGIN TRY
INSERT #WideKeyLab(ItemID,LookupValue)
VALUES(2,REPLICATE('X',1800));
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT ItemID,DATALENGTH(LookupValue) AS StoredBytes FROM #WideKeyLab;The long string is a chosen failure input, not an observed production record. The example shows why a deployment can appear successful while a later write fails. Test the boundary lengths accepted by the application's actual contract, including multi-column combinations. A valid business value should not become unwritable because an index was designed from a smaller sample.
Do not solve the failure by silently truncating the incoming value. That changes the data contract and can create false matches. Decide whether the declared business maximum should be smaller, the index key should be narrower, or another access design is required. Keep the decision visible to the application owner.
Find Wide Index Key Size Candidates in Metadata
The catalog records column maximum length in bytes. Sum the declared key columns and compare the result with the relevant ordinary rowstore limit. Included columns are not part of this key-byte sum.
SELECT s.name AS SchemaName,t.name AS TableName,i.name AS IndexName,
i.type_desc,SUM(CONVERT(bigint,c.max_length)) AS DeclaredKeyBytes,
CASE WHEN i.type=1 THEN 900 ELSE 1700 END AS KeyByteLimit
FROM sys.indexes AS i
JOIN sys.tables AS t ON t.object_id=i.object_id
JOIN sys.schemas AS s ON s.schema_id=t.schema_id
JOIN sys.index_columns AS ic ON ic.object_id=i.object_id AND ic.index_id=i.index_id
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 ic.key_ordinal>0 AND ic.is_included_column=0
GROUP BY s.name,t.name,i.name,i.type,i.type_desc
HAVING SUM(CONVERT(bigint,c.max_length))>
CASE WHEN i.type=1 THEN 900 ELSE 1700 END;This reports potential declared width, not the largest stored key value. Variable-length columns can leave a candidate index usable until a larger row arrives. Inspect the actual definitions and supported value ranges. Also review disabled indexes and planned indexes during deployment, because catalog presence alone does not describe the future write path.
For a particular composite string key, measure the relevant stored lengths together rather than taking each column's independent maximum and pretending they occurred in one row. Keep NULL handling explicit in the expression. Sampling can miss the longest combination, so combine measured evidence with the declared contract.
Keep Payload Outside the Search Key
If the query filters by a narrow identifier and only returns a long description, the description can be included rather than used as a search key. Included columns do not count toward the key-byte limit, although they still increase index storage and write maintenance.
CREATE TABLE #NarrowKeyLab
(
ItemID int PRIMARY KEY,
TenantID int NOT NULL,
LookupCode varchar(40) NOT NULL,
Description nvarchar(1000) NULL
);
CREATE INDEX IX_NarrowKeyLab_TenantCode
ON #NarrowKeyLab(TenantID,LookupCode)
INCLUDE(Description);
SELECT ItemID,Description
FROM #NarrowKeyLab
WHERE TenantID=10 AND LookupCode='A101';An included column does not provide the same key ordering or direct seek semantics as a key column. Preserve the actual filtering and ordering requirements when moving a column. A covering index is useful only when its access path matches the operation. Reducing index key size must not erase the reason the index was proposed.
A narrow surrogate clustered key can also reduce locator overhead in related nonclustered indexes. Evaluate uniqueness and business-key enforcement separately. Do not replace a meaningful unique constraint with an unrelated numeric key and leave the original business identity unenforced.
Use a Hash Only With Original-Value Verification
A fixed-length hash can help find candidates for long exact values. It cannot support prefix or range searches in the same way as the original string. Hash collisions require verification, and string collation semantics need deliberate treatment when the hash operates on a binary representation. The indexed computed column needs QUOTED_IDENTIFIER ON, which SSMS sets by default.
CREATE TABLE #HashLookupLab
(
ItemID int PRIMARY KEY,
LongValue varchar(2000) COLLATE Latin1_General_100_BIN2 NOT NULL,
ValueHash AS CONVERT(binary(32),HASHBYTES('SHA2_256',LongValue)) PERSISTED
);
CREATE INDEX IX_HashLookupLab_Hash ON #HashLookupLab(ValueHash);
DECLARE @Value varchar(2000)='Synthetic lookup value';
SELECT ItemID,LongValue FROM #HashLookupLab
WHERE ValueHash=CONVERT(binary(32),HASHBYTES('SHA2_256',@Value))
AND LongValue=@Value COLLATE Latin1_General_100_BIN2;The original comparison prevents accepting a hash collision as an equal value. The binary collation makes the example's case-sensitive comparison explicit, but trailing-space semantics still need consideration for the real contract. Case-insensitive or normalized business equality requires a reviewed canonical representation before hashing. Do not assume a hash automatically preserves the original column's collation behavior.
A unique hash alone does not enforce full original-value uniqueness reliably. Define a collision-aware enforcement design if that requirement exists. I treat the hash as an access aid and retain the authoritative value. The small key is helpful precisely because it is not the complete original information.
Validate Writes and Query Behavior Together
Which valid application value approaches the combined key limit? Include that case in deployment testing, along with updates that lengthen an existing key. Check the query's result and plan after choosing a narrower design. A fix that avoids an insert error but causes unacceptable lookup behavior needs another design review.
Review the write failure as part of the application error contract. A rejected indexed value can leave a transaction needing rollback, retry, or a clear user-facing explanation. Do not repeatedly submit the same oversized value and expect the storage boundary to change. Validate the incoming business constraint at the appropriate boundary while keeping the database definition consistent with that constraint. Include batch imports and background writers in the review, because their inputs can bypass an interactive form.
Document the accepted limits, index purpose, and any normalization or collision handling. Index key size is a physical constraint with application consequences. Resolve it through an explicit data and access contract rather than waiting for the longest valid row to discover the boundary in production.
Related reading on this blog: Multi-Column Index Key Order: Which Column Goes First and Query Listing All the Indexes Key Column with Included Column.
A successful index creation is not proof of future write safety, it is one check against the values present at that moment.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
