Monday 27 July 2015

Can we create non clustered index on a primary key column?



Yes we can create non clustered index on a primary key column!


According to the default behaviour of sql server, when we create a primary key column in a table then a clustered index will be automatically created on that particular column.

This default behaviour can be changed. And we can tell sql server not to create any index on primary key column. i.e, we can ask sql server to create primary key column without any index and some other column with clustered index.
So, this is one method in which we can create non clustered index on a primary key column. This is shown in the below way.


CREATE TABLE dbo.Table_1 (
Id int NOT NULL IDENTITY (1, 1) PRIMARY KEY NONCLUSTERED,SomeOtherUniqueColumn int NOT NULL UNIQUE CLUSTERED ) ON [PRIMARY]

No comments:

Post a Comment

GROUPBY VS PARTITIONBY