Hi Friends,

We all are aware about clustered and non clustered indexes in SQL Server. We are also aware about limitation of Non clustered index key length i.e. 900 bytes. That means you can’t have key length data size more than 900 bytes. I tried to explain the same thing in one of my blog post you can check that Here. Let me show you the same thing on SQL Server 2014 with Service Pack 1:

Now I am going to create a Non Clustered index on fname and lname columns, Which have 450 + 450 = 900 bytes key length, As per the key length limitation We can create this index.

Image1

Now I am going to create a Non Clustered index on Fname,Lname and City columns, Which have 450 + 450 + 400 = 1300 bytes key length, As per the key length limitation we can not create this index.

Image2

Let me now show you the same thing on SQL Server 2016 CTP 3.0. First Run the above first setup script to create the database, table and insert the data. After that create the index which failed on SQL Server 2014 SP1

Image3

Great, this time index has been created successfully with key length greater than 900 bytes. Now let me create a New Non Clustered Index with much wider index key:

Image4

As you can see in the above error message, this time it failed due to the key length of 1705 bytes. However, the maximum key length for Non Clustered index limited to 1700 bytes. That means Non Clustered Index Key Length in SQL Server 2016 CTP 3.0 has been increased from 900 bytes to 1700 bytes.

Reference

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook