SQL Server 2016 Increased Non-Clustered Index Key Size

Problem

Designing clustered and non-clustered indexes in SQL Server is an important part of SQL Server performance tuning. A well-designed index can significantly help enhance the performance of your queries by speeding up the process of retrieving the requested data. But when you design an index you need to take into consideration the index size limitation, especially when that index contains many key columns, or columns with large sizes. In SQL Server 2016, the maximum size of all index key columns has been extended to provide us with more flexibility when we design an index. In this tip, we will see how it works.

Solution

SQL Server provides us with the ability to create two main types of indexes:

The clustered index, in which the rows are stored and sorted physically on the disk in the order specified in the index. You can only have one clustered index on each table. If you don’t have a clustered index it is referred to as a heap.
The non-clustered index contains an ordered version of the index key columns with pointers to the physical rows located in the heap or the clustered index. You can create many non-clustered indexes for the table.

Adding many non-clustered indexes to your table can make performance worse, as these indexes will consume the disk space and slow down the insert/delete/update operations performed on that table. In addition there is extra effort required to maintain these indexes. So you need to create a minimum number of non-clustered indexes designed in a proper way to speed up your queries and the workload.

The decision of choosing which key columns will be used in the non-clustered index is not an easy job, due to the maximum total size allowed for the index key columns participating in the non-clustered index. You need to calculate the size of these key columns and make sure that it will not exceed the index limitation.

Let’s take a closer look and create a test table, IndexSizeTest, that contains five columns, one identity which is also the primary key on which the clustered index will be created automatically, and four string columns with different fixed sizes:

 USE MSSQLTipsDemo
GGO
CREATE TABLE IndexSizeTest
(IIID INT IDENTITY(1,1) PRIMARY KEY,
EMPName char(200),
EmpPhoneNumber char(200),mpAddress char(1300),
EmpPostCode char(500)
)

Once the table is created, we will insert 1000 rows into the table using the INSERT statement below:

 INSERT INTO IndexSizeTest VALUES ('John','+96288877455','Jordan-Amman','87452')
GO 1000

Testing on a SQL Server 2014 instance

First we will start by checking the maximum total size allowed for the index key columns in SQL Server 2014. The following T-SQL statement can be used to get the current SQL Server instance version, which is 2014 as shown below:

Assume that we plan to create a non-clustered index that contains three columns; EmpName, EmpPhoneNumber and EmpPostCode. The size of the index that will contains these columns can be calculated by querying the sys.columns catalog view using the T-SQL script below:

 SELECT SUM(max_length)AS IndexSize
FROM sys.columns
WHERE name IN (N'EMPName', N'EmpPhoneNumber', N'EmpPostCode')
AND object_id = OBJECT_ID(N'dbo.IndexSizeTest');

The total size of the columns participating in the non-clustered index is 900 bytes as shown below:

You can get the same result by simply summing the length of each column participating in the index:

200 + 200 + 500 = 900 bytes

If we try to create the index that will contains the previously mentioned columns, with a total size equal to 900 bytes using the following CREATE INDEX T-SQL statement:

 CREATE NONCLUSTERED INDEX IX_IndexSizeTest_Test1 ON IndexSizeTest (EMPName,EmpPhoneNumber,EmpPostCode)

The index will be created successfully:

Again, if we create another non-clustered index that contains three columns; EmpName, EmpPhoneNumber and EmpAddress. The size of the index that will contain these columns will be 1700 bytes as shown below:

SQL Server index size will be 1700 bytes

Trying to create the index that contains the previously mentioned three columns, with a total size equal to 1700 bytes this time, using the following CREATE INDEX T-SQL statement:

 CREATE NONCLUSTERED INDEX IX_IndexSizeTest_Test2 ON IndexSizeTest (EMPName,EmpPhoneNumber,EmpAddress)

The index creation will fail, showing that we exceed the maximum index size, which is 900 bytes:

SQL Server index creation fails because the maximum index size 900 bytes

Testing on a SQL Server 2016 instance

Let us now check the maximum total size allowed for the index key columns in SQL Server 2016. The current SQL Server instance version, which is 2016 can be retrieved as shown below:

If we try to create a non-clustered index that contains three columns; EmpName, EmpPhoneNumber and EmpPostCode, with a total size equal to 900 bytes, using the CREATE INDEX T-SQL statement below:

 CREATE NONCLUSTERED INDEX IX_IndexSizeTest_Test1 ON IndexSizeTest (EMPName,EmpPhoneNumber,EmpPostCode)

The index will be created successfully:

SQL Server index is successfully created

Also, trying create another non-clustered index that contains three columns; EmpName, EmpPhoneNumber and EmpAddress, with a total size equal to 1700 bytes, using the CREATE INDEX T-SQL statement below:

 CREATE NONCLUSTERED INDEX IX_IndexSizeTest_Test2 ON IndexSizeTest (EMPName,EmpPhoneNumber,EmpAddress)

This index will be created successfully this time, although it exceeds the previous 900 bytes size limitation:

SQL Server index created successfully although exceeds 900 bytes

Again, if we try create a non-clustered index that contains four columns this time; EmpName, EmpPhoneNumber, EmpAddress and EmpPostCode, with a total size equal to 2200 bytes, using the CREATE INDEX T-SQL statement below:

 CREATE NONCLUSTERED INDEX IX_IndexSizeTest_Test3 
ON IndexSizeTest (EMPName,EmpPhoneNumber,EmpAddress,EmpPostCode)

The index creation will fail, showing that we exceeded the maximum total size allowed for the index key columns in SQL Server 2016, which is 1700 bytes. This means that in SQL Server 2016, the maximum size for index keys with non-clustered index is increased to 1700 bytes, instead of the 900 bytes limitation in the previous SQL Server versions:

SQL Server index creation will fail exceeded the maximum total size for index key columns in SQL Server 2016, which is 1700 bytes

Using Include Columns

A workaround for the non-clustered index limitations, such as the maximum index size limitation, the maximum number of columns that can participate in the index, which is 16 key columns, and the allowed datatypes, is the use of the INCLUDE clause of the CREATE INDEX statement that is used to add non-key columns to the non-clustered index.

These non-key columns will not be calculated within the non-clustered index maximum size of 1700 bytes and 16 key columns limitations and will not be checked for the allowed data types limitations, except for text, ntext and image data types are not allowed.

If we try to create the index again, but this time we will add the large EmpAdress column to the INCLUDE clause, using the CREATE INDEX T-SQL statement below:

 CREATE NONCLUSTERED INDEX IX_IndexSizeTest_Test3 
ON IndexSizeTest (EMPName,EmpPhoneNumber,EmpPostCode) INCLUDE (EmpAddress)

The non-clustered index will be created successfully this time without reporting any error, as the considered size of the index will be the key columns size of 900 bytes:

Next Steps

Consider when calculating the estimated size of the non-clustered index that the size of columns with nchar and nvarchar data types is twice the number of characters specified in the CREATE TABLE statement.
Read more SQL Server Indexing Tips.
Ideally you want to keep your indexes as small as possible, but this shows there are index size limitations within SQL Server.

Resources

Community

Subscribe