Why SQL Server has 900 byte index size limit 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.
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.