Why 249 Non clustered Indexes?
-
Hai all,
Happy new year..
Is there any reason to have restriction on non clustered indexes(i.e 249) ?
Regards,
Ramesh K
-
You mean you need more indexes on a table ????
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I believe it's a byte issue, 256 possible values, -1 for no indexes (0), -1 for clustered (1), maybe a few system items, 255 might be locked out.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
I tend to agree Frank.
I hope you will never get even close to this theoritical limit otherwise your index maitenance jobs will be interresing and soooo loooong
Bye
Gabor
Bye
Gabor -
Flipping through MSDN, BOL and other I found no real explanation why there is this theoretical limit, but I think that 249 indexes (including PRIMARY KEY and UNIQUE CONSTRAINTS) are more than enough. In a real world application I doubt you would ever want to get even near this number.
I'm curious what effect this would have on performance
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
According to Inside SQL Server 2000 the allowable values for non-clusted indexes in the indid column is 2 to 250. That leaves 249 values. Not sure what happened with 251-254. 255 is reserved for tables with text and image data. 0 means a heap table (no clustered index) and 1 means a clustered index.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
Hey Steve, wouldn't this make a good case for another detective work like in your page size article?
...if not someone else comes up with the solution.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Apparently under SQL Server 4.2 if you had multiple text columns it would count down from 255:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;75191
This isn't the behavior in SQL Server 2000, so it's probably a hold-over. Out of curiosity, did SQL Server 4.2 limit the number of text columns on a table to 5? 251-255 = 5 values.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
I am too young to know this (haha). Members older than 35.5 to the front.
Btw, happy new year, Jonathan <grin>
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I've started with Sybase 4.2 after I've migrated several sites to MS SQL Server 6.0 but I do not remember to have a limit of 5 text columns in 4.2.
Bye
Gabor
Bye
Gabor -
It's because all indexes and statisitics have a reference stored in the sysindexes. The indid field is the unique id for the hard disk location of the data or index and it is a tinyint. 0 is the table (if stored as a heap) and 1 is the table if stored as a clustered index. 255 is any text/image data, which is stored seperately from the table. After that 2 - 250 are used by non-clustered indexes and table staticistics, so you can only have a total of 249 of both of these combined
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
Yup. The question on the table is "Was there a limit to the number of text columns in SQL Server 4.2?" SQL Server 4.2 had text column entries starting from 255 and counting down (this is not the behavior in SQL Server 2000). Of course, there would be a conflict if 250 = text column as 250 = non-clustered index.
K. Brian Kelley
@kbriankelley
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply