The index entry of length exceeds the maximum length of 1700 bytes for nonclustered indexes

SQL Server 2016 and Azure SQL database have increased the maximum size for index keys for non-clustered indexes.

The new maximum key size for non-clustered indexes is 1700 bytes.
The maximum key size for clustered indexes remains 900 bytes.

Error:

The index entry of length [1800] bytes for the index [IDX_NON_CLUSTERED_INDEX_NAME] exceeds the maximum length of 1700 bytes for nonclustered indexes.

When this exception raise?

While creating a nonclustered index on any column which has data more than 1700 characters or inserting data into an indexed column with more than 1700 characters.

Solution:

1. You can add a CHECKSUM column and then build a hash index on it, which can help improve indexing speed when the column is a long character column.

2. In nonclustered index, move the long character column from Indexed columns to Included columns.

3. Remove the long character column from nonclustered index. (Adding a long character column to index will decrease the performance)

Previous