Workaround maximum key length for a nonclustered index in SQL Server

Question

Is there a way to increase the limit for a nonclustered index which is 1700 bytes in SQL Server? When I build my database I get this warning:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'CS_UK' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.

Or do I have to change the structure of my indexes?

Solution 1

A workaround is to use include for the columns which are too big when creating the index.

CREATE NONCLUSTERED INDEX IX_IndexSizeTest_Test3 
ON IndexSizeTest (EMPName,EmpPhoneNumber,EmpPostCode) INCLUDE (EmpAddress) 

When EmpAdress in this example would be a very huge column we can add it to the index with INCLUDE(EmpAddress) because then it won't be counted when it comes to the index limitation. Here are more details about it.

Solution 2

Even if it were possible, you should consider changing your index structure. That being said, from the blog of the engineering team:

SQL Server 2016 and Azure SQL Database have increased the maximum size for index keys with nonclustered indexes. The new maximum key size for nonclustered indexes is 1700 bytes. The maximum key size for clustered indexes remains 900 bytes.

At the end of the article they add:

For memory-optimized tables: the maximum index key size for nonclustered indexes is 2500 bytes; there is no strict limit on index key size for hash indexes.

So it might be possible.

Comments

[nicolas2008]

I've downvoted your answer since it doesn't look correct for me. The main purpose of index is a fast lookup. Included columns can't be used for this purpose, they are literally included in index payload and helpfull when it's necessary to have these columns in query output

[Prashant]

@nicolay.anykienko you are right that included columns do bloat the index, however the question and the answer was about the hard limit, and the linked web page is clear on this: „[included nonkey columns] are not considered..when calculating…index key size“

[nicolas2008]

@eckes, the question was about index key. Included columns is not a part of index key.

[Prashant]

Thats what the answer says ,)