14

Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer.

I can think of this way to answer this question.

Page size 8K with usable 8060 900 bytes * 8 = 7200 bytes (900*9 = 8100), so only 8 records can fit in a page at max. If this index is a non clustered, it would also need to store clustered index key which can again be 900 byte long i.e. For a single record memory required would be (900 + 900 bytes) i.e. 4 records per page can be stored.

If only 4 records can be stored in a page, IO operations overhead compared to performance gain would nullify, hence index size limit is 900 bytes.

Improve this question
0

2 Answers 2

Reset to default
20

From Microsoft SQL Server 2008 Internals:

In fact, improving scalability is the primary reason for the limit to an index key of 900 bytes, or 16 columns, whichever comes first.

At 900 bytes per key, you can have a maximum of 8 index entries per page. As you decrease the maximum number of index entries per page, you vastly increase the number of levels required to store any given index. The smaller the key size, the more efficient the index, since it requires fewer pages of data.

As of SQL Server 2016, the maximum byte size for non-clustered indexes has been increased to 1,700 bytes, and 32 columns.

Improve this answer
0
0

There could be major debates on this point. But only correct answer could be provided by Microsoft itself.

https://msdn.microsoft.com/en-us/library/ms188783.aspx?f=255&MSPPError=-2147217396

Information about 1700 Bytes is provided on above link, but unfortunately no reason has been provided so far for 900 of 1700 Bytes limit of index length.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.