Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2004-01-07 : 13:25:00
|
| Hi,I am creating a non-clustered index on 3 columns and getting this warning message:Warning! The maximum key length is 900 bytes. The index 'UK_2_t_tab1' has maximum length of 1275 bytes. For some combination of large values, the insert/update operation will fail.What is the reason for getting this warning and how to avoid it?Thanks--rubs |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-01-07 : 13:26:18
|
| The maximum size allowed for an index key is 900 bytes, but SQL Server 2000 allows indexes to be created on columns that may have large variable type columns with a maximum size greater than 900 bytes. During index creation, SQL Server checks the following conditions: The sum of all fixed data columns that participate in the index definition must be less or equal to 900 bytes. When the index to be created is composed of fixed data columns only, the total size of the fixed data columns must be less or equal to 900 bytes. Otherwise, the index will not be created and SQL Server will return an error.If the index definition is composed of fixed- and variable-type columns, and the fixed-data columns meet the previous condition (less or equal to 900 bytes), SQL Server still checks the total size of the variable type columns. If the maximum size of the variable-type columns plus the size of the fixed-data columns is greater than 900 bytes, SQL Server creates the index, but returns a warning to the user. The warning alerts the user that if subsequent insert or update actions on the variable-type columns result in a total size greater than 900 bytes, the action will fail and the user will get a run-time error. Likewise, if the index definition is composed of variable-type columns only, and the maximum total size of these columns is greater than 900 bytes, SQL Server will create the index, but return a warning.You can read more about this behavior in BOL - Maximum Size of Index Keys |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-07 : 13:31:46
|
| Rubs,You will need to correct this situation or you will get failures on INSERT or UPDATE if ever the values for the 3 columns combined reach over 900 bytes. You need to figure out if the length of the columns is what you need. You might have one that doesn't need to be that long. Or you'll need to remove one column or two columns from the index, just depends on what makes it get under 900 bytes.Tara |
|
|
|
|
|
|
|
|
warning message during index creation