Non Clustered Index Key Length in SQL Server 2016 CTP 3.0

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:

Create Database Test
GO
Use Test
GO
Create table xttest
(
id int identity(1,1) PRIMARY KEY,
fname char(450),
lname char(450),
city char(400),
state char(400),
county varchar(50)
)
go
-- Here I am inserting 1000 rows of same kind of data
insert into xttest values('prince','rastogi','Gurgaon','Haryana','India')
go 1000

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

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.