Index key size

Setting

to

implicitly sets

to

when the database

compatibility level is set to 90 or higher. If the database compatibility level is set to 80 or

earlier, the

option must explicitly be set to.

If the

options are incorrect, the following conditions can occur:

Creating the filtered index fails.

The Database Engine generates an error and rolls back the

,

,

, or

statement that changes data in the index.

Query optimizer doesn’t consider the index in the execution plan for any Transact-SQL

statements.

For more information about filtered indexes, see

Create filtered indexes

and the

index architecture and design guide.

For information about spatial indexes, see

CREATE SPATIAL INDEX

and

Spatial indexes

overview.

For information about XML indexes see,

CREATE XML INDEX

and

XML Indexes (SQL Server).

The maximum size for an index key is 900 bytes for a clustered index and 1,700 bytes for a

nonclustered index. (Before SQL Database and SQL Server 2016 (13.x) the limit was always 900

1

1

SET

ANSI_PADDING
ON
ON
ON
OFF
ANSI_WARNINGS
ON
ON
ON
OFF
ARITHABORT
ON
ON
OFF
OFF
CONCAT_NULL_YIELDS_NULL
ON
ON
ON
OFF
NUMERIC_ROUNDABORT
OFF
OFF
OFF
OFF
QUOTED_IDENTIFIER
ON
ON
ON
OFF

ANSI_WARNINGS

ON

ARITHABORT

ON

ARITHABORT

ON

SET

INSERT

UPDATE

DELETE

MERGE