Link to home
Start Free TrialLog in
Avatar of VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY

asked on

clusters and non clusters

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?
ASKER CERTIFIED SOLUTION
Avatar of edlunad
edlunad
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY

ASKER

yes i knew that,but i like know why there is 249 non clusters?
SOLUTION
Avatar of Swadhin Ray
Swadhin Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 .
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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.
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.
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.