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

by

Troubleshooting Guide: SQL Server Error 1946 — Index Entry Exceeds Maximum Length

Why This Error Happens

This error occurs because SQL Server 2025 enforces a maximum index key size (900 bytes for standard indexes, 1,700 bytes for non-clustered indexes with included columns) and the combined size of the columns in your index key exceeds that limit. This is typically caused by indexing wide variable-length columns (such as NVARCHAR(450) or larger, or multiple concatenated VARCHAR columns) without accounting for row-level data growth. It can also surface after a data migration or schema change where column widths were increased but the index definition was never revalidated.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Index and Column Composition

  • Run the following query to find the index and its key columns:
SELECT i.name AS IndexName, c.name AS ColumnName, c.max_length, t.name AS DataType
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE i.name = 'YourIndexName';
  • Sum the max_length values of all key columns to confirm which ones push the index over the 900-byte (or 1,700-byte) threshold.

Step 2: Redesign the Index to Reduce Key Size

  • Move large, non-critical columns from the key list into the INCLUDE clause instead of the index key itself:
CREATE NONCLUSTERED INDEX IX_Example
ON dbo.YourTable (ShortKeyColumn)
INCLUDE (LargeVarcharColumn);
  • If possible, reduce column widths (e.g., change NVARCHAR(450) to NVARCHAR(200)) if business requirements allow.
  • Consider splitting a compound index into two smaller, more targeted indexes.

Step 3: Drop and Recreate the Problematic Index

  • Drop the existing index using SQL Server Management Studio (SSMS) or T-SQL:
DROP INDEX IX_Example ON dbo.YourTable;
  • Recreate it using the revised column structure from Step 2.
  • If this is a constraint-backed index (e.g., a unique constraint), drop and recreate the constraint instead:
ALTER TABLE dbo.YourTable DROP CONSTRAINT UQ_Example;
ALTER TABLE dbo.YourTable ADD CONSTRAINT UQ_Example UNIQUE (ShortKeyColumn);

Step 4: Rebuild Statistics and Validate on Windows Server 2025 / Windows 11

  • Open SQL Server Configuration Manager to confirm the instance is running normally post-change.
  • Run UPDATE STATISTICS dbo.YourTable; to refresh the query optimizer’s data after the index rebuild.
  • Check the SQL Server Error Log (via C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for any residual indexing warnings.

Alternative or Verification Steps

  • Re-run the original INSERT or UPDATE statement that triggered Error 1946 to confirm it now completes successfully.
  • If redesigning the index isn’t feasible, use a computed column with a hash (e.g., HASHBYTES('SHA2_256', ...)) as an indexed surrogate key for large text columns instead of indexing the raw column directly.