Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Increased Non-Clustered Index Key Size


By:   |   Read Comments (1)   |   Related Tips: More > Indexing


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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:

SQL Server 2014

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:

total size of the columns participating in the non-clustered index is 900 bytes

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:

SQL Server index 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:

SQL Server 2016

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:

SQL Server Included Columns
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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, August 05, 2017 - 9:17:13 PM - Randy in MArin Back To Top

Thanks for the tip.  It's good to have the added length when it abosultely required (e.g., conversion from mainframe).  I'm sure it will be abused.  The clustered index length maximum remains 900.  Would be nice to have a hidden RID of some sort that could be used instead of storing the clustered-index key internally non-clustered indexes.  I suppose if somebody really wants a 900 length key added to every non-clustered index....  Also, not hidding the RID would be nice at times (e.g., in triggers to relate inserted and deleted tables).   


Learn more about SQL Server tools