Geek Answers Handbook

Convert a clustered index to a non-clustered index?

Is it possible to convert a clustered index to a non-clustered index or to a non-clustered index for a clustered index in SQL Server 2005.

Convert this query to a clustered index:

create index index1 on mytable(firstcolumn)

Convert this query to a non-clustered index:

 create clustered index clusindex1 on mytable(cluscolumn)
+4
source share
3 answers

There is more 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

saying that you can only have one clustered index for each table, so if you try to reset the index and recreate it as a clustered index, it will fail if you already have a clustered index. Whenever you put a clustered index, all non-clustered indexes will also be discarded and recreated, pointing to the heap, and then fall again and recreated when the clustered index is created, now pointing to the clustered index (see WITH DROP_EXISTING clause)

I would say how to look for indexing in Books On Line before you start dropping and re-creating indexes

+5
source

These are not requests; These are DDL commands. Remove and recreate indices as desired, for example:

 drop index mytable.index1 go create nonclustered index index1 on mytable (firstcolumn asc) go
+3
source

I also wanted to know if a transformed (modified) cluster index could be a nonclustered index. I do not think it can be done. The existing clustered index must first be deleted, and then a new nonclustered index (possibly with the same name as the clustered index) must be created. The same is true for converting a non-clustered index to a clustered index.

I have no idea why you are asking for “queries” that need to be converted, but @Tahbaza is correct in that the code you included in your question is not really a query. These are T-SQL statements for making changes to "data definitions" (i.e., the schema [structure] of your database).

0
source

More articles:


All Articles