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.
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