How to convert a clustered index to non clustered index!!!

During an interview I was asked the below question.

Suppose you have table Employee with column ID, pancard and name. We have primary key on column ID and it has a clustered index on it.A non clustered index on pancard column. After few months you realized that the clustered index should be on  pancard, not on ID . So, can we convert this index from non clustered to clustered? If Yes then How?

As we all know when we create primary key column, by default it creates a clustered index on that field. We can have only 1 clustered index and 999 non-clustered index on a table.

The answer for above question is YES.. we can convert this index. What we have to do is quite simple. As we can have only one clustered index on a table So, first we need to change the clustered index to non clustered index and after that convert non clustered index to clustered index.

Lets create a Test database "IndexChange" and a table 'Employee" with same field then will see how can we achieve this.

use master

go

if exists (select * from sys.databases where database_id=DB_id('IndexChange'))

drop database indexChange

GO

CREATE DATABASE IndexChange

GO

USE IndexChange

GO

CREATE TABLE EMPLOYEE (EMP_ID INT CONSTRAINT EMP_ID PRIMARY KEY NOT NULL, PANCARD VARCHAR(25),NAME VARCHAR(30))

GO

INSERT INTO EMPLOYEE VALUES (1,'ABC123','CHAUBEY'),(2,'ABC124','JD'),(3,'ABC125','SUSHIL'),(4,'ABC126','VINAKAR')

GO

Create a non clustered index on PanCard column.

From below image we can see that EMP_ID is clustered and Pancard is non-clustered.Now, Go to object explorer. Select database then table then expand column>Right click to EMP_ID then Modify, In designer right click then select Index Keys. A new window will appear. Select Emp_ID. Go to Table designer. Under table designer you will see create as clustered. As this is primary key so the option is set to Yes. Change it to NO.

In designer select PANCARD then right click then select Index Keys. A new window will appear. Select PANCARD. Go to Table designer. Under table designer you will see create as clustered. Set it to YES for this column.  Now save it and close the designer. Now go to indexes again and you will find the indexes are changed vice versa. From clustered to non clustered and non-clustered to clustered. Below is the screen shot for our test database.So this is how we can change it. Thanks for reading this blog!!!