clusters and non clusters

VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY used Ask the Experts™
on
I like to know why there is 249 non cluster is implement?

is there any specify reason microsoft chosed for249?why not morethan  249 non-cluster aint implemented?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
No idea about the 249 number, but that was for 2005 and earlier, SQL Server 2008 and up supports up to 999 Nonclustered indexes per table

http://msdn.microsoft.com/en-us/library/ms143432(v=SQL.100).aspx
yes i knew that,but i like know why there is 249 non clusters?
Non cluster index can be crated on a table or a view and has a row in sys indexes.
The values in the row for each non clustered index range from 2 to 250 and the root column points is to top the non clustered index B-tree.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

The values in the row for each non clustered index range from 2 to 250 ... i what to know purpose of this number 2 to 250?what it actually meant for? why not less than 250 or greater than 250?
now in sql 2008 r2 we using 999.so i like to know why microsoft using this number.Any specific reason,actually meaning for those numbers.
Not sure absolutely .....
You can also put your question in attention by clicking on Request Attention link, so that more experts can give you a proper answer .
Ryan McCauleySenior Data Architect
Commented:
Kimberly Tripp has a great blog about SQL Server Indexing, and she addresses this question specifically:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Indexes_JustBecauseUCan_NO.aspx

The short answer is "because they wanted to", but she goes on to say that just because they've increased this number doesn't mean you should be creating more indexes. I'd suggest you read her post, since digs into the details and my summary here won't do it justice.
Top Expert 2015
Commented:
There is a limit of 999 non-clustered indexes per table in MS SQL v2008
Your database is suboptimal even when you reach 100 indexes. It means that one row update of table will cause 200 pages written to indexes and 2 writes to table... Slow indeed.

For performance you can have indexes on all cols that are in WHERE statements, and then prepare repeting statements in client side.

Java jtds driver will do that automatically.
Most Valuable Expert 2014
Commented:
I can't answer specifically, but I suspect it was a remnant of the 8K pages that SQL Server is built on. Probably because 255 is the largest number in an represented in an eight-digit binary number.
Top Expert 2015

Commented:
Another product of Watcom database does a lot better.
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/BABFHFGG.htm
But i still repeat that even 10 indices is an overkill in most cases.
I believe this is a classic case of, "There are decisions you make and those you live with . . . and this is one you didn't get to make." ;-)

You might as well ask why Microsoft chose to name a language C# rather than D-flat or even just D.
Top Expert 2015

Commented:
And I would also add that it is in no way related to clustering MS SQL server ;)
I've requested that this question be deleted for the following reason:

Since the answer is unknown, i have dropped a mail to microsoft. Waiting for their reply.Since answer is unknown , I am closing deleting the question.
Top Expert 2015

Commented:
You got ~10 different explanations. Just split fairly and add a result of your research.
Forgot to mention - Clustered indices have nothing to do with server clustering for HA.
Top Expert 2015

Commented:
You got the explanation that his is a design choice by Microsoft.
gheist:

whenever the design is made, there must be some logic in the backend.I like to know the backend idea for designing.
Ryan McCauleySenior Data Architect

Commented:
Some decisions are made just because a decision needs to be made. They had to prevent you from creating an unlimited number of indexes on a table so prevent a script run awry from wrecking havoc on your database. They picked 256, and then some customers complained that they actually had a reason for creating that many, so Microsoft relented and upped the number of allowed indexes to 1024. Again, no particular reason, but that's just the number they picked.

Not every decision reached has a specific explanation behind it, though it would be nice if it did - the group in charge of that decision (if it wasn't just the whim of some developer) ended up at 256. Why do some sites limit your password to 14 characters in length? Why are domains limited to a certain number of characters? Why did DOS format file names to 8.3 and not 10.3 or 14.4? Some decisions just are what they are.
Top Expert 2015

Commented:
Logic is simple. While in theory you can have a lot of indices, each new table line needs to update all the indices, with many of them simple updates become burdening.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial