8

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.

  • 1
    It doesn't answer the question, but you will understand something about: brentozar.com/archive/2013/05/indexing-wide-keys-in-sql-server – Racer SQL Nov 24 '15 at 18:59
  • I don't see the point of this question. You can't do it, because MS programmers have programmed it this way. Are you asking why have they programmed it this way ? – Rohit Gupta Nov 24 '15 at 19:43
  • @RohitGupta: I am not questioning them. It's just I am trying to think on the same lines what could have been a reason they came up with this restriction. – Shantanu Gupta Nov 24 '15 at 20:09
  • @RafaelPiccinelli: though it's not connected to answer I am seeking for but It's a nice link to read. Good to know additional approach. :) – Shantanu Gupta Nov 24 '15 at 20:18
12

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.

  • 7
    It was an arbitrary selection but some reasonable limit had to be chosen. Note that in SQL Server 2016, the limit has been increased to 1700 bytes (for non-clustered indexes) and 32 columns. – Aaron Bertrand Nov 24 '15 at 19:27
  • If so, why the limit has been raised to 1700 in sql server 2016. What different MS has done in this release. – Shantanu Gupta Nov 24 '15 at 19:42
  • 2
    I expect they increased the limit in SQL Server 2016 since newer hardware is a lot more capable. That is only a guess. – Max Vernon Nov 24 '15 at 19:44
  • @ShantanuGupta Not sure it matters why they've increased the limit, but probably a variety of reasons - to support wider indexes in general, for one (people have long complained about the 900 byte limit, in spite of performance), and also for new wider values such as those produced by Always Encrypted (when using deterministic encryption). – Aaron Bertrand Nov 24 '15 at 20:08
  • @MaxVernon: I am thinking on the same lines. I would keep this answer open for more edits/comments for another 1-2 days until I research more on this. Thanks for your help :) – Shantanu Gupta Nov 24 '15 at 20:17
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.