Index entry exceeds the maximum length of 900 Bytes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Index entry exceeds the maximum length of 900...

Index entry exceeds the maximum length of 900 Bytes

Rate Topic

Display Mode

Topic Options

Author

Message

abhisheksharmacnn

Posted Wednesday, March 06, 2013 5:04 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 10:59 PM
Points: 459, Visits: 47

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.

Post #1427320

anthony.green

Posted Wednesday, March 06, 2013 5:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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)

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1427322

ChrisM@Work

Posted Wednesday, March 06, 2013 5:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1427332

ChrisM@Work

Posted Wednesday, March 06, 2013 5:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1427333

Lynn Pettis

Posted Wednesday, March 06, 2013 7:04 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,627, Visits: 27,480

abhisheksharmacnn (3/6/2013)

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.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1427368

« Prev Topic | Next Topic »

Permissions