a table can have how many indexes

Posted by Jameschowdare under Sql Server on 12/3/2009 | Views : 5824 | Status : [Member] | Replies : 5
a table have how many cluster index have,
limitaion of non cluster indexes, and how many we create for a table




Responses

Posted by: Bubbly on: 1/25/2010 [Member] Bronze

Up
0
Down
Hello,

A table can have 1 clustered Index and 249 non-clustered index, so in-total 250 index

++
Thanks & Regards,
Deepika

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/28/2013 [Member] Starter | Points: 25

Up
0
Down

Please always mark as answer if Answer is correct this helps others

Question : A table can have how many indexes in SQl server ?

2005 and below:

250 indexes 249 NON clustered and 1 clustered

2008 and Above:

1000 indexes 999 NON cluustered and 1 clustered





Visit my blog
www.tutorialsqlserver.com


Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 12/4/2009 [Member] [MVP] Silver

Up
0
Down
Hi

1. A table can have 250 Indexes
2. One CLUSTERED Index
3. 249 NON-CLUSTERED Index

1. Script for Creating Table:
DECLARE @intA INT,
@SQLs NVARCHAR(MAX)

SELECT @intA = 1
SELECT @SQLs = 'CREATE TABLE TBL_LimitValidation('

WHILE(@intA<=1024)
BEGIN
SELECT @SQLs = @SQLs + 'COL' + CAST(@intA AS VARCHAR) + ' INT,'
SELECT @intA = @intA + 1
END

SELECT @SQLs = LEFT(@SQLs ,LEN(@SQLs)-1) + ')'

EXEC SP_EXECUTESQL @SQLs

2. Script for Creating NON-CLUSTERED Index :
DECLARE @intA INT,
@SQLs NVARCHAR(MAX)

SELECT @intA = 1

WHILE(@intA<=1024)
BEGIN
SELECT @SQLs = 'CREATE NONCLUSTERED INDEX IND_TBL_LimitValidation' + CAST(@intA AS VARCHAR) + ' ON TBL_LimitValidation('
SELECT @SQLs = @SQLs + 'COL' + CAST(@intA AS VARCHAR) + ')'
EXEC SP_EXECUTESQL @SQLs
SELECT @intA = @intA + 1
END

See the Err message
"Could not create nonclustered index 'IND_TBL_LimitValidation250' because it exceeds the maximum of 249 allowed per table or view."

Cheers



Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Abhi2434 on: 12/4/2009 [Member] [Microsoft_MVP] [MVP] Silver

Up
0
Down
Non Clustered index generally overlaps, so there might come a situation where lots of redundant data and 1 insert statement creates hundreds of Index entries.

But if disk space is not your constraint, you should use it.

Also if you recreate a table having non - clustered index, do drop indexes as well first.

www.abhisheksur.com

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Komminedi on: 12/24/2009 [Member] Starter

Up
0
Down
A table can have only one clustered index
and 256 non clustered index.

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response