converting clustered index into non-clustered index?

sql sql-server clustered-index non-clustered-index
naveenkumar picture naveenkumar · Aug 29, 2010 · Viewed 12.5k times · Source

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)

Answer

SQLMenace picture SQLMenace · Aug 29, 2010

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

Related questions

Why is there a scan on my clustered index?

SQL 2000 The NED table has a foreign key to the SIGN table NED.RowID to SIGN.RowID The SIGN table has a foreign key to the NED table SIGN.SignID to NED.SignID The RowID and SignID are clustered primary …

Read More
Does SQL Server creates Non clustered index by default

Ya, it is a duplicate of this. But I just needs a clarification on this article by Pinal Dave, which says the following: Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index In this case …

Read More
Clustered index on temp table

I'm trying to optimize a procedure that has code like the following: CREATE TABLE #t1 (c1 int, c2 varchar(20), c3(varchar(50)...) CREATE CLUSTERED INDEX ix_t1 ON #t1(c3) ON [PRIMARY] I wanted to improve that by moving the CLUSTERED …

Read More