Export (0) Print
Expand All

Maximum Size of Index Keys

SQL Server 2000

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

Show:
© 2014 Microsoft