Maximum Size of Index Keys

Maximum Size of Index Keys

Microsoft® SQL Server™ 2000 retains the 900-byte limit for the maximum size of an index key but changes the algorithm used by CREATE INDEX to check if the specified index key exceeds the maximum allowable key size of 900 bytes. The new CREATE INDEX algorithm is similar to the row size algorithm used for CREATE TABLE.

Microsoft SQL Server version 7.0 and earlier always used the maximum size of variable columns when checking whether the key specified in a CREATE INDEX statement exceeded 900 bytes, for example:

CREATE TABLE TestTable
    (PrimaryKey      int PRIMARY KEY,
     VarCharCol1     varchar(500),
     VarCharCol2     varchar(500)
    )
-- This statement fails because the maximum sizes
-- of the two columns exceeds 900 bytes:
CREATE INDEX TestIdx ON TestTable(VarCharCol1, VarCharCol2)

In SQL Server 2000, the preceding CREATE INDEX statement succeeds with a warning message, unless one or more rows of data will generate a key whose value exceeds 900 bytes.

The SQL Server 2000 CREATE INDEX statement uses these algorithms:

• If the size of all fixed columns plus the maximum size of all variable columns specified in the CREATE INDEX statement is less than 900 bytes, the CREATE INDEX statement completes successfully with no warnings or errors.
  
  
• If the size of all fixed columns plus the maximum size of all variable columns exceeds 900, but the size of all fixed columns plus the minimums of the variable columns is less than 900, the CREATE INDEX statement succeeds with a warning that a subsequent INSERT or UPDATE statement may fail if it specifies values that generates a key value larger than 900 bytes. The CREATE INDEX statement fails if existing data rows in the table have values that generate a key larger than 900 bytes. A subsequent INSERT or UPDATE statement that specifies data values that generates a key value longer than 900 bytes fails.
  
  
• The CREATE INDEX statement fails if the size of all fixed columns plus the minimum size of all variable columns specified in the CREATE INDEX statement exceeds 900 bytes.

This table shows the results of creating indexes where the keys contain only fixed or only variable-length columns.

Index Columns
Size of the fixed-data column(s)	Maximum size of variable-length column(s)	MAX of the SUM of the index key column lengths*	Index created	Message	INSERT or UPDATE run-time error due to oversized index key value
> 900 bytes	None	Not relevant	No	Error	No index present to generate error.
< = 900 bytes	None	Not relevant	Yes	None	No
None	< = 900 bytes	Not relevant	Yes	None	No
None	> 900 bytes	> 900 bytes	No	Error	No index present to generate error.
None	> 900 bytes	< = 900 bytes	Yes	Warning	Only if the sum of current lengths of all index columns is greater than 900 bytes.

* None of the rows in the table at time the CREATE INDEX statement is executed can have index key values whose total lengths exceed 900 bytes.

This table shows the results of creating indexes where the keys contain a mixture of fixed and variable-length columns.

Index Columns
Minimum size of variable-length column(s) + Size of the fixed-data column(s)	Maximum size of variable-length column(s) + Size of the fixed-data column(s)	MAX of the SUM of the index key column lengths *	Index created	Message	INSERT or UPDATE run-time error due to oversized index key value
> 900 bytes	Not relevant	Not relevant	No	Error	No index present to generate error.
< = 900 bytes	< = 900 bytes	Not relevant	Yes	None	No.
< = 900 bytes	> 900 bytes	< = 900 bytes	Yes	Warning	Only if the sum of current lengths of all index columns is greater than 900 bytes.
<= 900 bytes	> 900 bytes	> 900 bytes	No	Error	No index present to generate error.

* None of the rows in the table at time the CREATE INDEX statement is executed can have index key values whose total lengths exceed 900 bytes.

See Also

CREATE INDEX