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

19 October 2023 | Viewed 743 times

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