Search code examples
sqlsql-serverdatabaset-sql

Workaround maximum key length for a nonclustered index in SQL Server

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

  • 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.