creating a clustered index

Question

I created a primary key on a table with the default options. I want to create an index with clustered index I have to drop the primary key and reccreate a nonclustered index and alter it to clustered index. Why can't I create an clustered index after dropping the index? Why should I creaete nonclustered index first and alter it?

Answer 1

If you declared a primary key, you already have a clustered index. 

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

You pretty much answered your own question.

If you create a primary key using default options it will default to creating it as a clustered key.

Create your primary key as a non-clustered key then you can create a separate clustered index:

alter table dbo.test add constraint
	pk_test primary key nonclustered (col1, col2)

---

ajmer dhariwal || eraofdata.com

Answer 3

Phil,

Are you saying that if I create a table with a primary key a clustered index will be automatically created?

Don't I have to create a clustered index separately through an explicit create index command?

Answer 4

Phil,

Are you saying that if I create a table with a primary key a clustered index will be automatically created?

Don't I have to create a clustered index separately through an explicit create index command?

When you create a table and define column(s) as PRIMARY KEY, a clustered index is automatically created. 

See the BOL page for CREATE TABLE: http://msdn.microsoft.com/en-us/library/ms174979.aspx  (Specifically the options for PRIMARY KEY and CLUSTERED, where it states that CLUSTERED indexes are the default for PRIMARY KEYs)

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 5

Yes that's correct, just run the below query on your table you can see Cluster index in the index description column.

sp_helpindex TableName