Creating primary key without clustered index

One of my colleague asked me a question that “Can we create a primary key without a clustered index?”. I answered him that “Yes, of-course!” and also did not forget to share this information to all of you in this post named “Create nonclustered primary key”. I know many of us are well aware of this and have did this many times in our day to day SQL activity. Still for those who don’t know how they can accommodate a primary key with a nonclustered index, i am sharing this post, in which i will show you that how we can create a non-clustered primary key on a table.

Before creating this nonclustered primary key, here i am showing you the default behavior of SQL Server during Primary key creation. In case table does not have any Clustered index created on it, SQL Server creates a PRIMARY KEY on a clustered index by default. It’s a default behavior of SQL Server. In case we want to create a nonclustered primary key, we have to explicitly define it during primary key creation. As we know that a table can has only one clustered index created on it. Because clustered index orders the table data as per it’s key and thus we can order a table in only one way. We can use multiple columns to order a single table but in only one way we can order the table.

To understand the default behavior of SQL Server during primary key creation, have a look on below example;

Default behavior of SQL Server during Primary Key Creation Demo

Create a table as below;

CREATE TABLE TEST
(
ID INT NOT NULL,
NAME CHAR(10)
)
GO

Add a primary key on it as below and remember that we do not have any clustered index on this table yet;

ALTER TABLE TEST
ADD CONSTRAINT PK_TEST PRIMARY KEY (ID)
GO

Have a look on the index type created in this case;

SELECT object_name(object_id) AS TableName, type_desc, NAME, index_id, type FROM SYS.indexes WHERE object_name(object_id) = 'TEST'
GO

From above figure we can ensure that the created index type is “Clustered” index.

Now what will happen if we have a clustered index on the table already? Just follow the below scripts to see it live;

Drop the created table as below;

DROP TABLE TEST

Create it again;

CREATE TABLE TEST
(
ID INT NOT NULL,
NAME CHAR(10)
)
GO

Create a clustered index on this table before creating a primary key constraint on it;

CREATE CLUSTERED INDEX IX_TEST_NAME ON TEST(NAME)
GO

Now create a primary key as below;

ALTER TABLE TEST
ADD CONSTRAINT PK_TEST PRIMARY KEY (ID)
GO

Now check the type  of primary key as below;

SELECT object_name(object_id) AS TableName, type_desc, NAME, index_id, type FROM SYS.indexes WHERE object_name(object_id) = 'TEST'
GO

Just have a look on primary key index type, it’s a “nonclustered” index.

Finally come to the main question of this post “how we can create a nonclustered primary key?” in below section.

Nonclustered Primary Key Creation

Create a demo table as below;

CREATE TABLE TEST1
(
ID INT NOT NULL,
NAME CHAR(10)
)
GO

Now add a nonclustered Primary key as below;

ALTER TABLE TEST1
ADD CONSTRAINT PK_TEST1 PRIMARY KEY NONCLUSTERED (ID)
GO

Have a look on the types;

SELECT object_name(object_id) AS TableName, type_desc, NAME, index_id, type FROM SYS.indexes WHERE object_name(object_id) = 'TEST1'
GO

In above figure, we can see that table does not have any clustered index yet, but this primary key is being created on a nonclustered index. Table is still on Heap.

Conclusion

To create a nonclustered primary key we have to mention it explicitly during primary key creation. You can notice it in Alter Table statement of “Nonclustered Primary Key Creation” section. Again i am highlighting here that ADD CONSTRAINT PK_TEST1 PRIMARY KEY NONCLUSTERED (ID) in this statement i have defined the type of primary key as NONCLUSTERED.

Thanks for reading my post “Create nonclustered primary key” and please share it on your social media if you really like this and don’t forget to rate and share your comments.