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.|
-- CREATE TABLE testNCI ( Id VARCHAR(1700) ) GO CREATE NONCLUSTERED INDEX ix_Id ON testNCI(Id) GO --OUTPUT Commands completed successfully. --
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.
|☛ 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:
My SQL Page on Facebook:
2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/
Enjoy !!! Keep Learning