Warning the Maximum Key Length is 900 bytes – How To Resolve

If you create an index on a table that includes a column that has the potential to have data of greater than 900 bytes you will see a warning like this.

Warning! The maximum key length is 900 bytes. The index ‘some index name’ has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.

This is a real gotcha.

maximum key length

So what does this mean? What is going to happen if data greater than 900 bytes gets inserted, will it completely fail?

The best way to find out is with an example so lets create a table, some data and an index to recreate this scenario and see what happens.

------------------------------------------
USE DBAFire_DB

CREATE TABLE MaximumKey (id int, Bigfield NVARCHAR(500))

INSERT INTO MaximumKey 
SELECT 1, 'Some interesting stuff here'

--create index
CREATE NONCLUSTERED INDEX ix_maxkey ON MaximumKey(id, bigfield) 

-- show how it can be the biggest.
SELECT SUM(max_length)AS TotalIndexKeySize
FROM sys.columns
WHERE name IN (N'Bigfield')
AND object_id = OBJECT_ID(N'MaximumKey');
------------------------------------------

Now we have set up and reproduced the warning message.

Now lets try to add some text that is greater than 900 bytes, in this case we will build an ever increasing string into the varchar field.

------------------------------------------

USE DBAFire_DB

WHILE (SELECT LEN(Bigfield) FROM MaximumKey) < 1000

BEGIN
UPDATE MaximumKey
SET Bigfield = Bigfield + '$$$$$$$$$$$$$$$$$$$$$$'

END

------------------------------------------

When the field gets extended greater than 900 bytes you will get an error like below.

Msg 1946, Level 16, State 3, Line 4
Operation failed. The index entry of length 911 bytes for the index ‘ix_maxkey’ exceeds the maximum length of 900 bytes.

How to resolve this issue

What you can do is use the column as part of an includes in the index instead of as one of the key columns.

Replace the index that was created before with this.

--create index
CREATE NONCLUSTERED INDEX ix_maxkeyincludes ON MaximumKey(id) INCLUDE (bigfield)
-------------------------------------------- 

Thats about the only way we have seen that will help with this kind of issue.

Alternatively if you don’t require the field to be that large you could reduce the size of it to under 900 bytes. E.g if it is a NVARCHAR(500) drop to a NVARCHAR(400) if possible.

Have you encountered this issue before and how did you work around it?

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.