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.
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.
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
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:
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.
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
