SQL SERVER – Maximum Number of Index per Table

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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – SQL Server Management Studio New Features
Next Post
SQL SERVER – 2008 – Policy-Based Management – Create, Evaluate and Fix Policies

Related Posts

49 Comments. Leave new

  • Why we have 249 non clustered index in sql server 2005? why not 240 or 300? and the same question for sql server 2008, why 999 ? Why not 800 or 1000?

    Reply
    • the total indexes are 250 (including 1 clustered index) for 2005 and 1000 (including 1 clustered index) from version >2005. So they are 250 and 1000 in total

      Reply
  • For a table we can create 250 columns per table in 2005.so, per table one clustered index and others are non-clustered index.so ,totally (1+249) indexes per table..

    Hope this helps,
    Deepa

    Reply
  • Pinal,

    You are GOD of sql server.

    Thanks for always help us.

    Reply
  • Hitesh Jadhav
    July 27, 2012 9:41 am

    Pinal Sir,
    Can we create index on primary key without drop constriant PK ..??
    If yes then index in automatically created when we set PK key then how it will be use full to DB .. May be this will be silly question but guide me

    Reply
  • In a Table we can create 255 / 1024 columns , then why can’t create more than 250 / 1022 indexes per table

    Reply
  • ravikant sharma
    January 26, 2013 9:19 am

    Hi

    Thanks for your responses.

    I have 3+ million records in my table with 1 clustered index. Now I am doing a report that will search the rest of the fields. ie. other than the unique fields.

    So.. If i am going to set non-clustered index to 20+ columns. I thought if the no. of rows increases to 5-10+ million records then what will happen? In this situation should I re-index the table in periodically??

    Am I right? My thought may be wrong.. Please clarify

    Thanks
    Ravi

    Reply
  • In SQL 2012 how many cluster index we can create.

    Reply
  • @Srinivasan
    numbers are same for SQL 2008/R2 and 2012 i.e. 1 Clustered Index + 999 Nonclustered Index = 1000 Index

    cheers!
    Deepak Garg

    Reply
  • what factor decides max number of non-clustered indexes allowed on table ..
    whats that difference in SQL 2008 (as compared to SQL 2005) which increased the limit from 249 to 999.. Could you please throw some light on this.

    Thanks,
    Tarriq

    Reply
  • Sir How many non-clustered index are allowed in sql-Server 2012

    Reply
  • satish matta
    July 9, 2015 7:34 am

    I have a query Why we have 249 non clustered index in sql server 2005? why not 240 or 300? and the same question for sql server 2008, why 999 ? Why not 800 or 1000?

    Reply
    • That question need to be asked to someone who thought of those magical numbers. I don’t have any explanation. but tell me this – who creates those many?

      Reply
  • In 2012 and 2014 how many Non-Clustered Indexes?

    Reply
  • how many clustered and non- clustered index created in sql server 2012 & 2014

    Reply
  • Shivam Kumar Sharma
    August 5, 2016 7:47 pm

    Unique index = non-clustered index. That means 249 unique index for 2005 and 999
    unique index for 2008

    Reply
  • How many Nonclustred and clustered index in SQl 2012 & 2014 ?

    Reply
  • How many Nonclustred and clustered index in SQl 2012 & 2014 ?

    Reply
  • We can create 1 Clustered and 999 Non-Clustered Index in SQL Server 2008 , 2012 , 2014 and 2016

    https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017

    Reply
  • pinal sir

    if we create non non clustered index in each columns then what will happen??

    Reply
  • if we create non clustered index in each columns of the table then what will happen??

    Reply

Leave a Reply

Menu