SQL INTERVIEW QUESTION – What is the Maximum Length for creating non-clustered index in SQL SERVER?

This is a recent question I have received on my email – What is the Maximum Length for creating Non-Clustered Index(NCI) in SQL SERVER?

Let us see the answer with the help of below table.

SQL Version Bytes per index key – NON CLUSTERED INDEX
Before SQL SERVER 2016 900
SQL SERVER 2016(CU2+) 1700

The key column length should not exceed 900 bytes but with SQL SERVER 2016(Cumulative Update 2) it is 1700 bytes. Now lets read morde details from MSDN.

SQL Server Database Engine object   Maximum sizes/numbers SQL Server (64-bit) Additional Information
Bytes per index key   900 bytes for a clustered index. 1,700 for a nonclustered index. The maximum number of bytes in a clustered index key cannot exceed 900 in SQL Server. For a nonclustered index key, the maximum is 1700 bytes.
      You can define a key using variable-length columns whose maximum sizes add up to more than the limit. However, the combined sizes of the data in those columns can never exceed the limit.
      In a nonclustered index, you can include extra non-key columns, and they do not count against the size limit of the key. The non-key columns might help some queries perform better.

Example

--

CREATE TABLE testNCI
(
	Id VARCHAR(1700)	
)
GO

CREATE NONCLUSTERED INDEX ix_Id ON testNCI(Id)
GO

--OUTPUT
Commands completed successfully.

--

Reference

https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com