Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index entry exceeds the maximum length of 900 Bytes Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 5:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 25, 2013 2:05 AM
Points: 489, Visits: 48
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
Posted Wednesday, March 6, 2013 5:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,143, Visits: 4,948
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
Posted Wednesday, March 6, 2013 5:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 7,133, Visits: 13,519
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
Posted Wednesday, March 6, 2013 5:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 7,133, Visits: 13,519
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1427333
Posted Wednesday, March 6, 2013 7:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 23,077, Visits: 31,605
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.



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 »

Add to briefcase

Permissions Expand / Collapse