How many columns can be included in an index in SQL Server?

How many columns can be included in an index in SQL Server?

SQL Server limits. Currently, you can have no more than 16 key columns in an index, and altogether those key columns cannot exceed the maximum index size of 900 bytes. Included columns can be data types that are not allowed as index columns.

What are included columns in an index?

Included columns in a non-clustered index can only be the non-key columns. In the included columns the storage only happens on the leaf level of the index, contrary to the key column of an index. The key column of an index is stored at all levels.

What is included column in non-clustered index?

An index created with included columns is a non-clustered index that also includes non-key columns in the leaf nodes of the index, similar to a clustered index. There are a couple benefits to using included columns.

Can we create non-clustered index on multiple columns?

In SQL Server, you can include up-to 1023 columns per each non-clustered index. But you have to add minimum one key column to your non-clustered index in order to create it.

What is index key column?

Index key column is the column used in the index, and used by the optimizer to decide of the index is applicable. It’s also used by the execution engine to fulfill the WHERE part of the query. This is equivalent to the stuff in the back of a book. It is used for the SELECT part of the query if the column is needed.

How many columns can be included in a nonclustered index?

Include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 32 key columns and a maximum index key size of 1,700 bytes (16 key columns and 900 bytes prior to SQL Server 2016 (13.x)).

Can a nonkey column be included in an index?

They can be data types not allowed as index key columns. They are not considered by the Database Engine when calculating the number of index key columns or index key size. An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns.

How are included columns different from key columns?

In the included columns the storage only happens on the leaf level of the index, contrary to the key column of an index. The key column of an index is stored at all levels. The non-key columns don’t have the same limitations as the index key columns in terms of the data type.

What are the disadvantages of including columns in an index?

If you want to decide whether the usage of included columns in your indexes makes sense, you need to consider the characteristics of the database. The disadvantages are reduced performance of data modification operations and as mentioned before the lower buffer cache efficiency and the increased I/O demands in case of the use of LOB datatypes.