I have a large table with about 200 million records. I want to define a composite nonclustered key on it. What is the maximum number of columns per index key in Ms SQL Server 2014?
There are two limitations which you would probably have to consider.
- The maximum number of columns in an index key is 16, unless you have an XML column
Columns per index key
Note: If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns. For more information, see Create Indexes with Included Columns.
The maximum number of bytes in an index key is 900.
Bytes per index key
Note: The maximum number of bytes in any index key cannot exceed 900 in SQL Server. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes.
Maximum Capacity Specifications for SQL Server (Microsoft)