Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Which of the following is not valid index configuration for a table on SQL server 2008?

  1. A partitioned clustered index and a partitioned non_clustered index.
  2. A non clustered index and many clustered indexes.
  3. A clustered index and many non clustered indexes.
  4. no indexes.
share|improve this question

closed as not a real question by Aaron Bertrand, StanleyJohns, Marian, ypercubeᵀᴹ, Mark Storey-Smith May 20 '13 at 14:15

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

Is it your exam or homework question? If it is, please specify it in your question. – Yasir Arsanukaev Apr 25 '13 at 8:33
@YasirArsanukaev – 孔夫子 Apr 25 '13 at 8:40
#2. is invalid. – StanleyJohns May 20 '13 at 3:59

Indexes are optional on SQL Server tables. In fact, you can add constraints (primary key, unique) within the CREATE TABLE statement but you cannot add indexes at the same time. The indexes come later - this on its own should convince you that a table must be able to be created without indexes. This rules out option (4).

In Clustered and Nonclustered Indexes Described, it is plainly stated that a table can only have one clustered index.

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

This immediately points option (2) as the invalid configuration. However, there are exceptions depending on how you interpret the statement. In a very loose interpretation, the data of the table can be clustered multiple times, using INDEXED VIEWS. An indexed view is an alternate storage of the table data along a different physical order, to assist in queries that may benefit from approaching the data in this new order.

So there is a little bit of doubt if this loose interpretation was considered for the question. Let's move on to the other two options.

For option (3), we already know from one of the links above (Clustered and Nonclustered Indexes Described) that a table can only have one clustered index. We need to know if it can have many non-clustered indexes. From the same MSDN page, it does not explicitly state that you can have multiple non-clustered indexes. Let's look instead at Maximum Capacity Specifications for SQL Server - go down to Nonclustered indexes per table where it states 999. Okay, so now we know that we can have multiple non-clustered indexes per table.

With solid documentation out of the way, a more real-life understanding of indexes would lead you to realize that it is beneficial to have multiple indexes per table. Consider a table containing a catalogue of library books. It is beneficial to index the table by category, title, author at the same time, to speed up searches by any of those attributes (information) about the books. In the old days of pen and paper before databases, you may have three "index" stacks of sheets with the books ordered in different ways: e.g. by Author, by Title. On the Author sheet, you may find an entry for "C.S.Lewis" and next to it several Dewey decimal numbers "pointing" to the books - the numbers being the physical shelving order of the books. Incidentally, this "physical ordering" represents the clustered index - of which there can only be one.

Answer option number (1) is the last one we will look at. Partitioned indexes is fairly advanced so I won't go into detail. Suffice to say that the previous link (Maximum Capacity Specifications for SQL Server) doesn't give a limit for it separately to non-partitioned indexes, so statement (1) is as true as statement (3).

The answer to your question is (2).

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.