Can We Create Primary key with Clustered Index in SQL Server? Please explain with examples. [Resolved]

Posted by Neerajprasadsharma under Sql Server on 5/12/2017 | Points: 10 | Views : 367 | Status : [Member] | Replies : 3
It is a trick question, especially for the beginners,
Primary key and Clustered Index are different concepts, yet related to each other.
Clustered index is one of the reasons to use a primary key.
If there Clustered index have not created already on the table then by default SQL Server creates a Clustered index on the table.
Example Below:

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


View index type with sp_Help 'TableWithPK'

see this article:http://www.dotnetfunda.com/articles/show/3496/how-to-create-primary-key-in-sql-server

I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.


Responses

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

Up
1
Down

Resolved
The Primary key is actually a constraint , according to the ANSI SQL Standard, which identifies the row uniquely in the table. We can create a Primary Key on one or more than one column and those column(s) has to be NOT NULL according to ANSI standard.
The SQL Server is a Relational Database which follows most of the ANSI standard SQL, it has implemented the primary key as described in the ANSI SQL Standard.

Since Primary key and Clustered Index are different concepts solves different Problems in the Databases, So SQL Server allows to create a primary key without Clustered index as well. But it's a default setting of SQL Server, if you create a Primary Key in SQL Server it will automatically create Clustered Index on that column for you, which usually a good thing for you, but there are some exceptions where it's not always a good idea to create a clustered index of the primary key.

To understand this answer as a whole, we need to understand Clustered index as well there are many articles available online on this subject, but I like Gail Shaw`s http://www.sqlservercentral.com/articles/Indexing/68563/ and kimberly L Tripp https://www.sqlskills.com/blogs/kimberly/category/clustered-index/,
we strongly recommend, please look into the above link to have better command over this subject.

Back to the above question again, If we create a Primary Key in SQL Server, by default it creates Clustered index in SQL Server, but we are allowed to create Primary key without Clustered Index as well.

As We can`t post Image in the Question Answer Section, we will combine all the question and answer and put it in one article with images and example.

Other Related Question in series:

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.


I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

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

Posted by: Jitendrasoft09 on: 5/13/2017 [Member] [MVP] Starter | Points: 25

Up
0
Down
First we need to understand basic that clustered index is set by default in primary key. please refer it in below for more details.
http://stackoverflow.com/questions/11864754/can-i-have-a-primary-key-without-clustered-index-also-can-i-have-multivalued-c

Jitendra Kumar
If my post helps you, plz mark as an answer.

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

Posted by: Neerajprasadsharma on: 5/14/2017 [Member] Bronze | Points: 25

Up
0
Down
Hi
Thanks for the answer, I am new to this section and I thought I can self answer the question.
I have been posting a beginners guide to help those are new to SQL Server. My aim to introduce them to SQL Server
As easy way as possible, so I try to make articles as short and to the point only.
Now i want to take those articles to the next level. So I decided to post a self
explainationary question and answer, as many as i can and link to the my basic article.
Basicy i want to make a one stop to that subject.
As a SQL Server developer I learnt some things in hard way with the exprience,
and now i want to make a collection of all the questions and answer in one place to help the community.

My first topic was the primary key in SQL Server. So I want to post all the possible
Questions and answers about primary key and link back to the article.
The above was first question in series and i tried to self answer that question and seems like
It was insufficient. So I will try to answer again.

I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

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

Login to post response