TechEd on Road Ahmedabad, June 20, 2009, was a huge success. This grand event saw over 200 attendees actively participating in the sessions. We had attendees traveling from far and wide, including Delhi, Mumbai, Jaipur, Kerala, Baroda, Himmatnagar, Rajkot, among other cities from India. This enthusiastic participation made the event truly grand. It was a moment of bliss for me as I had not anticipated such tremendous positive response!
Although the Official time to commence the event was at 1:45 PM we were really excited to see the attendees entering the hall before the official time. We were more than happy to quickly arrange a special session for those attendees who came in early to keep them engaged. They won loads of gifts while waiting for the official sessions to start. We had a brainstorming quiz session. I was happy to see that people in Ahmedabad are now taking keen interest in enhancing their knowledge.
One of the questions was – What is the maximum number of Index per table? I received lots of answers to this question but only two answers are correct. Let us now take a look at them.
For SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx
For SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index
http://msdn.microsoft.com/en-us/library/ms143432.aspx
I would like to thank one of the attendees of Ahmedabad TechEd Kruti Kansara for the above comment.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
SQL Server 2005 link is currently pointing to SQL Server 2000.
Link for SQL Server 2005 :
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx
~ IM.
I think the maximum number of indexes on a table in 2008 is 1000 (1 Clustered + 999 Nonclustered Indexes).
Pinal,
It should be 1000 indexes for SQL Server 2008.
I suppose you meant 100 indexes for SQL 2008 :)
Yes, Imran is right. The link for SQL Server 2005 is referred to SQL Server 2000.
Also I am little confused for SQL Server 2008 as it shows
1 Clustered Index + 999 Nonclustered Index = 250 Index
Is it not 1000 Index?
Thanks,
DV
Your total for SQL2008 isn’t right either (1+999 = 1000).
Of course the likelihood is that any table that hits the maximum limits is part of a bad design. Can anyone think of any scenario where even 250 nonclustered indexes might be necessary?
A little fault in your addition:
1 Clustered Index + 999 Nonclustered Index = 250 Index
Must be 1000 Index ;-)
Thank you Imran and everybody.
Error has been fixed.
Kind Regards,
Pinal
Hi Friends,
Can any one tell me why we cannot create more than 249 Non-Clustered Indexes??
Thanks in advance,
Vijay Vasudevan
@Vijay: You are using SQL Server 2005, right? Read the post above or: http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx
Can you tell me what’s the maximum number of unique indexes in a table in sql server 2005 ?
@Darshan,
There are no unique indexes in any version of SQL Server.
There are basically two types of indexes.
Clustered Indexes and Non-Clustered Indexes.
In SQL Server 2005 :
you can have
1 Clustered Index on a table and
249 Non Clustered Indexes.
Reference: Books Online
Follow this link :
http://www.sql-server-helper.com/sql-server-2005/maximum-capacity-specifications.aspx
But, When ever you create a unique constraint on a column in a table, it automatically creates a non-clustered indexes.
A unique Key constraint cannot be created with out an index, either the index could be Clustered or Non-Clustered.
So your question could be, How many unique constraints you can have on a table or how many Non-Clustered indexes you can have on a table.
Answer is 249. (Given, no other Non-Clustered index is created Separately)
~ IM.
There are 2 types of indexes
1. Clustered Indexes
2. Non-clustered Indexes
There can be only one Clustered index in a table
and there can be 249 non-clustered indexes on a table