converting clustered index into non-clustered index?

Question

Is it possible to convert a clustered index to non clustered index or non clustered index to clustered index in sql server 2005.

Please convert this query into clustered index:

create index index1 on mytable(firstcolumn)

Please convert this query into non clustered index:

create clustered index clusindex1 on mytable(cluscolumn)

Solution 1

There is more to it than meets the eye

to create a clustered index

drop index mytable.clusindex1 
go

create clustered index clusindex1 on mytable(cluscolumn)

to create a non clustered index

drop index mytable.clusindex1 
go

create index clusindex1 on mytable(cluscolumn) --non clustered is default

having said that, you can only have one clustered index per table, so if you try to drop an index and recreate it as a clustered index it will fail if you already have a clustered index. Whenever you drop a clustered index all non clustered indexes will also be dropped and recreated pointing to the heap, and then again dropped and recreated when you create the clustered index, now pointing to the clustered index (look up the WITH DROP_EXISTING clause)

I would say lookup how indexing works in Books On Line before you start dropping and recreating indexes

Solution 2

Those aren't queries; they are DDL commands. Drop and recreate the indexes as desired, like so:

drop index mytable.index1
go

create nonclustered index index1 on mytable (firstcolumn asc)
go