How to Create a Primary Key Without Clustered Index? [Resolved]

Posted by Neerajprasadsharma under Sql Server on 5/15/2017 | Points: 10 | Views : 2841 | Status : [Member] | Replies : 1
By default SQL Server creates clustered index on the primary key, so is it possible to create primary key without clustered index?

Contact for Free SQL Server Performance Consulting and Training for you or your Organization.


Responses

Posted by: Neerajprasadsharma on: 5/15/2017 [Member] Bronze | Points: 50

Up
1
Down

Resolved

Yes , it is possible to create primary key without clustered index, by default when you don`t specify the index type SQL Server creates Clustered Index on it, and there is a good reason why SQL Server do it, we will look into it in the next question of the series.
The question is straightforward. How to create primary key without Clustered index, look below the script.

There are two ways to do that first one is direct.
Direct Way to create Non Clustered Primary Key:

when you create a primary key, then at the time of creation you can declared it as Culstered or Non-clustered.
This is documented in the BOL link https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql but if you do not specify the index type at the time of table creation by default it will be Clustered.

Example:

Create Table [T1] (ID Int CONSTRAINT PK_ID PRIMARY KEY Clustered , name Varchar(10) not null ,Country Varchar(10) )

SP_help 'T1'



Create Table [T2] (ID Int CONSTRAINT PK_ID PRIMARY KEY NONClustered , name Varchar(10) not null ,Country Varchar(10) )

SP_help 'T2'


Indirect Way:

The second way of creating the Nonclustered Index on primary key is based on the Clustered Index rule. Which says there can only be one Clustered Index per table. So if you create a Clustered Index on the table before defining a Primary Key on the table and then you create a Primary Key, SQL Server will automactically create NonClustered Index On the table.
Example:

Create Table [T3] (ID Int  not null , name Varchar(10) not null ,Country Varchar(10) )
Create Clustered index IX_name on [T3] (name)
ALTER TABLE [T3] add CONSTRAINT PK3_ID primary key (ID)
go

SP_help 'T1'




Other Related Question in series:
Can We Create Primary key with Clustered Index in SQL Server?
http://www.dotnetfunda.com/forums/show/22462/can-we- create-primary-key-with-clustered-index-in-sql-server-please-ex
How to create a primary key without Clustered index?
Why SQL Server by default creates clustered index on the primary key?
Can we create a Primary key without clustered index?
Can we create primary key without any index?
Can we create primary key on more than one column?
Can we have a functinality of primary key without creating a primary key?
Why primary key doesn`t accepts null values?
What is the need of primary key?
Is it mandotary to have a primary key in the table?

We will try to answer all the above questions and link all of them to each other,
We welcome everybody to contribute to this question answer series.

Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Neerajprasadsharma, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response