CODEWITHSUNDEEP
sql-server
ASH
ASH

Reputation: 20362

Multiple Clustered Indexes on a Single Table?

I thought we could only place one clustered index on one table, and put multiple non-clustered indexes on a table, but using the code below I can easily add more than one clustered index to my table.

CREATE CLUSTERED INDEX TBL_MULTI_LC_HIST ON dbo.TBL_MULTI_LC_HIST (ID,AsOfDate)

Is this completely wrong?

Upvotes: 6

Views: 16299

Answers (2)

pmbAustin
pmbAustin

Reputation: 3980

You can only have one clustered index. A "Clustered" index IS the row... it contains all the columns. Every other index would just contain a pointer to the clustered row. The key of the clustered index enforces an 'ordering' on the rows by default.

If there is no clustered index, then the rows are basically stored in a heap, with no order or structure.

Upvotes: 6

DavidG
DavidG

Reputation: 119206

It isn't possible to create multiple clustered indexes for a single table. From the docs (emphasis mine):

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 stored in only one order.

For example this will fail:

CREATE TABLE Thing
(
    Column1 INT NOT NULL,
    Column2 INT NOT NULL
)

CREATE CLUSTERED INDEX IX1 ON dbo.Thing(Column1)

CREATE CLUSTERED INDEX IX2 ON dbo.Thing(Column2)

Error:

Cannot create more than one clustered index on table 'dbo.Thing'. Drop the existing clustered index 'IX1' before creating another.

Example: http://www.sqlfiddle.com/#!18/53a63/1

You can however have a single index with multiple columns in it which is perhaps where you are getting confused:

CREATE CLUSTERED INDEX IX3 ON dbo.Thing(Column1, Column2)

Upvotes: 14

Related Questions