| Author |
Topic |
|
mukkanti
Starting Member
9 Posts |
 Posted - 2007-09-10 : 09:31:22
|
| hi can someone tell me why there are only 249 non-clustered indexes,is there any significance to that number |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 10:19:44
|
Is it important to you to be able to create MORE than 249 indexes on a single table?From BoL:quote:
Each table has a set of rows in sysindexes: - A heap has a row in sysindexes with indid = 0. The FirstIAM column points to the IAM chain for the collection of data pages for the table. The server uses the IAM pages to find the pages in the data page collection because they are not linked together.
- A clustered index on a table or view has a row in sysindexes with indid = 1. The root column points to the top of the clustered index B-tree. The server uses the index B-tree to find the data pages.
- Each nonclustered index created for a table or view has a row in sysindexes. The values for indid in the rows for each nonclustered index range from 2 through 250. The root column points to the top of the nonclustered index B-tree.
- Each table that has at least one text, ntext, or image column also has a row in sysindexes with indid = 255. The column FirstIAM points to the chain of IAM pages that manage the text, ntext, and image pages.
Kristen |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 10:58:27
|
| Sorry about that, I replied instead of Editing the post, and it looked stoopid so I deleted it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 10:58:46
|
If I needed the post-padding I'd post twice, rather than post & delete  |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-10 : 11:35:10
|
quote:
Originally posted by Kristen
If I needed the post-padding I'd post twice, rather than post & delete
If I needed the post padding I would respond to the post about post padding with another post.  Also, even though one would probably never use 249 indexes it is still always nice to know the reason behind the limit.
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 12:32:38
|
| Personally it exceeds anything I could conceivably need, so I aint interested in why that is the limit.Kristen |
|
|
|
mukkanti
Starting Member
9 Posts |
Posted - 2007-09-11 : 05:42:04
|
| Hey i just want to know the reason behind the no 249 for non-clustered indexes ,i am not going to create more than 249 non-clustered indexes,i would appreciate if anyone answer me |
|
|
|
mukkanti
Starting Member
9 Posts |
Posted - 2007-09-11 : 05:43:19
|
Thanks kristen i got the answer.quote:
Originally posted by Kristen
Is it important to you to be able to create MORE than 249 indexes on a single table?From BoL:quote:
Each table has a set of rows in sysindexes: - A heap has a row in sysindexes with indid = 0. The FirstIAM column points to the IAM chain for the collection of data pages for the table. The server uses the IAM pages to find the pages in the data page collection because they are not linked together.
- A clustered index on a table or view has a row in sysindexes with indid = 1. The root column points to the top of the clustered index B-tree. The server uses the index B-tree to find the data pages.
- Each nonclustered index created for a table or view has a row in sysindexes. The values for indid in the rows for each nonclustered index range from 2 through 250. The root column points to the top of the nonclustered index B-tree.
- Each table that has at least one text, ntext, or image column also has a row in sysindexes with indid = 255. The column FirstIAM points to the chain of IAM pages that manage the text, ntext, and image pages.
Kristen
|
|
|
|
|
non clustered indexes