Increased nonclustered index key size with SQL Server 2016

SQL Server 2016 and Azure SQL Database have increased the maximum size for index keys with nonclustered indexes. The new maximum key size for nonclustered indexes is 1700 bytes. The maximum key size for clustered indexes remains 900 bytes.

Consider, for example, the following script which creates a table with a 1700 byte variable-length character column, creates an index on that column, and inserts a 1700-byte value in the column.

 DROP TABLE IF EXISTS dbo.t1
GO
CREATE TABLE dbo.t1
( c1 VARCHAR(1700)
)
GO
CREATE INDEX ix_c1 ON dbo.t1(c1)
GO
INSERT t1 VALUES (REPLICATE('1', 1700))
GO

This script succeeds in SQL Server 2016 and Azure SQL Database. In earlier versions of SQL Server the CREATE INDEX statement would give a warning, and the INSERT statement would fail.

For more details see the Maximum Capacity Specification for SQL Server.

Two notes about columnstore indexes and memory-optimized tables:

• When specifying a nonclustered columnstore index you can specify the columns to be stored in columnar format. This is not actually an index key, and there are no strict size limitations on the columns included in a nonclustered columnstore index.
• For memory-optimized tables: the maximum index key size for nonclustered indexes is 2500 bytes; there is no strict limit on index key size for hash indexes.

• Anonymous
  April 26, 2016
  What's it for?