Index limitation in SQL Server
-
Hello,
Why SQL Server allows only 249 nonclustered indexes on a table? Is there any specific reason or just it's a random number? YOur response appreciated.
Murali
-
FIrst off 249 is a lot of indexes to have aon any one given table and the processing needed to manage these on inserts, updates and deletes would be extrodinary.
However my gut feeling is this is just a holdover from early Sybase or SQL developement and revolves aroudn the storage in sysindexes or the pages themselves. Without confirming anywhere I would hazard a guess that the original indid (or index id) value used to be a tinyiny type and certain were marked as off limits in the design (you need one to id heaps, one for a clustered, maybe something for unique or other). Now however indid is a smallint but imposing the 249 limit probably just make sense from design of the actual product because again that many indexes would be very heavy on even the best system.
But as for a real answer I don't know and have no documentation around why nor can I find any old documentation or an early 4.x copy of sql to see if the size was changed on the indid field for sysindexes.
Would be an iteresting thing to know from a historical and developement standpoint but most likely it had a menaing at one time that was just lost and no update to it was seen as needed.
-
Interesting enough here is another post on this last year http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=19701 and they seem to hint and my thoughts as well.
-
Yes, and since last year I haven't found an explanation what indid 251-254 are used for. I don't suspect them to be reserved for future use, since indid is of SMALLINT datatype and can therefore hold potentially much more indexes. However, rephrasing my post from the other thread, in real world you will almost never get near this limitation. Otherwise you have more serious problems with your whole schema than with this number of indexes. But what I think is a little bit unclear and vague explained in BOL, is that sysindexes does not only hold information on indexes but also on statistics. So, while you *can* create 249 nonclustered indexes, you are not able to create any statistics thereafter. This will give an error. Vice versa, when you've created statistics first, you are not able to create 249 nonclustered indexes. Consider this script:
IF OBJECT_ID('test_indexes') > 0
DROP TABLE test_indexes
GO
DECLARE @stmt NVARCHAR(4000)
DECLARE @i TINYINT
SET @stmt = 'CREATE TABLE test_indexes(c0 INT PRIMARY KEY'+CHAR(10)
SET @i = 1
WHILE @i <= 249
BEGIN
SET @stmt = @stmt + ' , c' + CAST(@i AS NVARCHAR) + ' TINYINT' +CHAR(10)
SET @i = @i + 1
END
SET @stmt = @stmt + ')'
EXEC sp_ExecuteSQL @stmt
SELECT
SUBSTRING('YesNo', 4 - 3 *
OBJECTPROPERTY(OBJECT_ID('test_indexes'),'TableHasClustIndex'),3)
AS Clustered_Index_vorhanden
, SUBSTRING('YesNo', 4 -3 *
OBJECTPROPERTY(OBJECT_ID('test_indexes'),'TableHasNonClustIndex'),3)
AS Index_vorhanden
SET @i = 1
WHILE @i <=249
BEGIN
SET @stmt = 'CREATE UNIQUE NONCLUSTERED INDEX ix'
+ CAST(@i as NVARCHAR)
+ ' ON test_indexes(c'+ CAST(@i AS NVARCHAR) + ')'
EXEC sp_ExecuteSQL @stmt
SET @i = @i + 1
END
SELECT
SUBSTRING('YesNo', 4 - 3 * OBJECTPROPERTY(OBJECT_ID('test_indexes'),'TableHasClustIndex'),3) AS Clustered_Index_vorhanden
, SUBSTRING('YesNo', 4 -3 * OBJECTPROPERTY(OBJECT_ID('test_indexes'),'TableHasNonClustIndex'),3) AS Index_vorhanden
SELECT
CAST(o.name AS CHAR(30)) AS Tabellenname
, CAST(i.name AS CHAR(30)) AS Indexname
, i.indid AS IndexID
FROM
sysindexes i
JOIN
sysobjects o
ON
i.id = o.id
AND i.indid BETWEEN 1 AND 254
AND o.type = 'u'
--AND i.name NOT LIKE '_WA%'
WHERE o.name = 'test_indexes'
/*
CREATE UNIQUE NONCLUSTERED INDEX
ix_testoverflow
ON
test_indexes(c1, c2)
*/
CREATE STATISTICS test_me_indexes
ON test_indexes (c1, c2 )
WITH SAMPLE 5 PERCENT
GO
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply