Maximum Size of Index Keys

[sukishan]

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.