Maximum index key size

Each database imposes a maximum size for index keys, also known as composite indexes.

For Microsoft SQL Server

The maximum number of fields in an index key is 16.

The maximum size of an index key is 900 bytes.

To determine the size of a composite index key, Microsoft SQL Server totals all the fixed columns plus the minimums of all the variable columns to determine if the composite index is within the allowable maximum size of an index. However, if there is already data in the table, and if the existing data in a column allows the total bytes of the index to be greater than the maximum size, then the index cannot be created. Also, if after the index is created data is inserted (or updated) that allows the maximum value of the index to exceed the maximum value size, then an error occurs at that time.

For example: You can define an index consisting of column1 with a size of 500 bytes and column2 with a size of 500 bytes. If you put 500 bytes of data in column1 and 500 bytes of data in column2, you get an error. If you put 100 bytes of data into column1 and 100 into column2 there is no error.

For Oracle

The maximum number of fields in an index key is 32.

The maximum total size of an index key is 70% of the database block size minus some overhead.

To determine the database data block size in SQLPlus:

Log on as sysdba.
At the SQL>, enter show parameters db_block_size

To determine the size of an index key, the Oracle server totals the maximum defined sizes of all the variable length fields plus the sizes of all the fixed length fields.

For DB2

The maximum number of fields in an index key is 16.

The maximum total combined length of a unique index key is 1024.