INdex
-
Why can be only 249 non clustered index on a table. Expalin.
-
Counterquestion: Isn't that enough?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Counter_answer : that's already too many if you want my opinion. But I guess that it may become handy in very large data warehouses to be able to add more than 16 indexes on a table.
-
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
You obviously *can* create up to 249 nonclustered indexes on a single table. But unfortunately sysindexes also stores statistics. So, when you've created that much indexes, you can't create a single statistic. Not the best approach...
I agree this 249 number is the theoretical limit. When you are getting anywhere near this limit, you certainly do have other problems than indexes.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sql server 2000 supports a max of 249 nonclustered indexes
**ASCII stupid question, get a stupid ANSI !!!** -
hi ,
its ok. it is sufficient number.
BUt i want to knoe what facts behind this limit on index.
in archtectural view or else.
thnx in advance
-
I don't know for sure, but I suspect this number to be more or less randomly chosen. When you look at indid in sysindexes you see, that it's data type is smallint. So there's plenty of room for future use. For now this is just another system or design limitation.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply