How to fix Error 1944 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 1944 – Index Key Size Exceeded

Why This Error Happens

This error occurs because SQL Server enforces a maximum byte limit on index keys—900 bytes for standard indexes (clustered/non-clustered) and 1,700 bytes for non-clustered indexes with included columns in certain configurations. When the combined size of the key columns (including implicit columns like the clustering key added to non-clustered indexes, or uniquifier overhead) exceeds this threshold, SQL Server refuses to create the index to prevent page-storage violations. This is commonly triggered by wide VARCHAR, NVARCHAR, or multi-column composite indexes, especially when the underlying table’s clustering key is also large and gets silently appended to every non-clustered index.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Columns and Calculate Key Size

  • Run sp_help 'TableName' or query sys.indexes and sys.index_columns joined with sys.columns to list every column in the index definition.
  • Manually sum byte sizes: NVARCHAR(n) = 2 bytes per character, VARCHAR(n) = 1 byte per character, INT = 4 bytes, BIGINT = 8 bytes, etc.
  • Don’t forget to add the size of the clustering key if you’re building a non-clustered index — it’s implicitly included.
  • Example diagnostic query:
SELECT c.name, c.max_length, c.system_type_id
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = OBJECT_ID('YourTableName');

Step 2: Redesign the Index to Reduce Key Width

  • Convert wide key columns to included columns instead of key columns, since included columns are stored at the leaf level and don’t count toward the 900-byte key limit.
  • Example rewrite:
CREATE NONCLUSTERED INDEX IX_Example
ON dbo.YourTable (ShortColumn1, ShortColumn2)
INCLUDE (LongColumn3, LongColumn4);
  • If possible, shrink oversized VARCHAR(MAX) or NVARCHAR(4000) columns down to only the length actually required by the business logic.

Step 3: Re-evaluate the Clustering Key

  • If the base table’s clustering key itself is wide (e.g., a composite key of multiple large columns), consider switching to a narrow surrogate key like an INT or BIGINT identity column.
  • Run the following to inspect current clustering key width:
EXEC sp_helpindex 'YourTableName';
  • Rebuilding the clustered index with a narrower key will reduce the implicit overhead added to all related non-clustered indexes.

Step 4: Recreate the Index and Validate

  • Drop the failed index definition if a partial object exists:
DROP INDEX IF EXISTS IX_Example ON dbo.YourTable;
  • Re-run the CREATE INDEX statement with the adjusted key/include column structure.
  • Confirm no error 1944 is thrown in SQL Server Management Studio (SSMS) or Azure Data Studio output pane.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes to confirm the new index exists and has is_disabled = 0 and is_hypothetical = 0.
  • As an alternative, if you cannot redesign the schema short-term, use a filtered index or computed column with a hash (e.g., CHECKSUM or HASHBYTES) to index a smaller representative value instead of the full wide column.