Index entry exceeds the maximum length of 900 Bytes

Question

Post reply

Index entry exceeds the maximum length of 900 Bytes

abhisheksharmacnn

Say Hey Kid

Points: 713
More actions
March 6, 2013 at 5:04 am

#394323

I am getting following error while creating index
Error/Warning Message is :-
Warning! The maximum key length is 900 bytes. The index 'IDX_TERMX' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.
Msg 1946, Level 16, State 3, Line 3
Operation failed. The index entry of length 904 bytes for the index 'IDX_TERMX' exceeds the maximum length of 900 bytes.
The statement has been terminated.
Please let me know if there is any resolution.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Drop the index

Insert data less than or equal to 900 bytes (900 varchar, 450 nvarchar)

Change the column to VARCHAR(900) or NVARCHAR(450)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Can you post the script for the index?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ChrisM@Work (3/6/2013)
Can you post the script for the index?

-- and the ddl for the table.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

abhisheksharmacnn (3/6/2013)
I am getting following error while creating index
Error/Warning Message is :-
Warning! The maximum key length is 900 bytes. The index 'IDX_TERMX' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.
Msg 1946, Level 16, State 3, Line 3
Operation failed. The index entry of length 904 bytes for the index 'IDX_TERMX' exceeds the maximum length of 900 bytes.
The statement has been terminated.
Please let me know if there is any resolution.

Simple, modify the columns used in the index such that the total max length of all the columns is less than or equal to 900 bytes.

SQL Server has a hard limit of 900 bytes for the size of an index.