Index exceeds maximum length in SQL Server

Question

I am getting below error. How to fix it? Why am I getting this error?

Message operation failed. The index entry of length 1526 bytes for the index ix_Emp_no_1 exceeds the maximum length of 900 bytes.

Answer 1

Maximum Bytes per index key

• 900 bytes for a clustered index.
• 1,700 for a nonclustered index

Result and meaning:

• When you have a nchar(450) because Unicode characters occupy 2 bytes it will equal 900 bytes.
• you can ignore Unicode and use char(900) instead.
• for variable strings types like varchar and nvarchar it seems to be working in the same manner.
• if you have composite key containing multiple columns the sum of them have to have lower bytes than this limitations.

Note : sometimes the SSMS designer does not let you create index larger than 900 bytes (till 1700 bytes) but it is officially supported. you just need to generate the script and run it yourself.

source : https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017

Answer 2

I have never seen this, but I found this article that may help you deal with your situation. https://blogs.msdn.microsoft.com/bartd/2011/01/06/living-with-sqls-900-byte-index-key-length-limit/

It sounds like you have a key that is over 900 bytes, a large text field maybe. So you need to find a different way to identify that row. Try to index a different column and include the problem column.