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 (http://blog.SQLAuthority.com)

About these ads

39 thoughts on “SQL SERVER – Maximum Number of Index per Table

  1. I think the maximum number of indexes on a table in 2008 is 1000 (1 Clustered + 999 Nonclustered Indexes).

  2. 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

  3. 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?

  4. @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.

  5. 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

    • Well we can have a unique index. You just need to use Unique key word in Create Index statement.
      /*Sample script*/
      Use tempdb

      Create table test1 (col1 int, col2 int)
      Create unique index idx_uni1 on test1(col1)

      So in short, an index could be clustered and non-clustered as well as unique and non-unique(total 4 variations). A unique index will not allow duplicate entry in the table based on the index key columns.
      By default any index is non-cluster and non-unique unless specified otherwise in create statement.

  6. In SQL 2005

    1 Cluster Index
    249 Non Cluster Index

    Total 250
    ———————————–
    In SQL 2008

    1 Cluster Index
    999 Non Cluster Index

    Total
    1000

      • Cluster Index Physically Stores data, or arranges data in one order (depends on which column(s) you have defined Clustered index and in which order.

        As a fact we all know that set of data can be only stored in only one order, that is why only one clustered index is possible.

        You can create a view on top of table and then you can created Clustered index on that view.

        Hope this helps.
        ~ IM.

  7. Two questions:

    * What’s the number of indexes in SQL Server ?
    * Do statistics occupy ‘slots’ of this number. In other words clusterd index + non clustered + statistcs = 250 (SQL 2000/SQL 2005?)

  8. Two questions:

    * What’s the number of indexes in SQL Server 2000 ?
    * Do statistics occupy ’slots’ of this number. In other words clusterd index + non clustered + statistcs = 250 (SQL 2000/SQL 2005?)

    thnx!

  9. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  10. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31 Journey to SQLAuthority

  11. 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?

  12. 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

  13. 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

  14. 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

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s