Why SQL Server by default Creates Clustered Index on the primary key? [Resolved]

Posted by Neerajprasadsharma under Sql Server on 5/17/2017 | Points: 10 | Views : 533 | Status : [Member] | Replies : 3
Do you know why the SQL Server by default Creates Clustered Index on the primary key why not Non Clustered index?

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


Responses

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

Up
1
Down

Resolved

Forget SQL Server, every system comes with some default setting, default settings are not the best settings always, but quite good in most of the situations.
For example: your computer comes with default brightness, Default mouse speed, default icon types, default background screen etc etc. That doesn`t mean it is best setting for you, that means it is quite good in general and customizable to user requirement.
This same applies to SQL Server as well.
Primary keys can be a good candidate for a clustered index. Not always, If you are following this series you might have seen this question
"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 where we posted links on Clustered Index key. You can read on that link, we should consider four attribute while creating the Clustered Index.

Narrow : Narrow Clustered keys are preferred because of two main reasons, first Clustered key always goes to Non-Clustered Index leaf level. If the Clustered key is very wide than it will increase the page size of all the non-clustered indexes on that table, more page size means more IO and more CPU cycles. Second, Wide Clustered Index will lead more pages even in the clustered index and even clustered Index seeks has to travel using the intermediate pages to fetch the rows.


Unique : Clustered index has to be unique, if it is not unique then SQL Server adds a hidden 4 bytes integer with every row, and put this in every Non Clustered Index leaf level as well to link the non-clustered index to the clustered index, If making the cluster unique requires adding several wide columns, then it may be better to keep the index narrower and leave it as not unique.

Static : As above mentioned, the clustering key goes to the leaf level of all Non Clustered Index, if the clustering key changes, then update has to happen in all Non Clustered Index as well. And Clustered key "in the Clustered Index" had to move to the current page and this leads page fragmentation.

Ever Increasing : If the Clustered index key is ever increasing like numeric integer 1,2,3 etc... Then every row will insert at the end of the last key, so no page split happen and would be less prone to fragmentation. Hence, faster writes and reads.

Primary key is certainly unique and most of the time it is Narrow, static and ever increasing as well.
If DBA/Developer is not experienced than he can make a wrong decision in choosing the Clustering key, and this will lead more IO and CPU cycles. That's probably why Microsoft has decided to make a clustered index on the primary key by default. And this can certainly changeable depending on the requirement of the table.



EDIT ="in the Clustered Index"




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

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

Up
1
Down

Forget SQL Server, every system comes with some default setting, default settings are not the best settings always, but quite good in most of the situations.
For example: your computer comes with default brightness, Default mouse speed, default icon types, default background screen etc etc. That doesn`t mean it is best setting for you, that means it is quite good in general and customizable to user requirement.
This same applies to SQL Server as well.
Primary keys can be a good candidate for a clustered index. Not always, If you are following this series you might have seen this question
"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 where we posted links on Clustered Index key. You can read on that link, we should consider four attribute while creating the Clustered Index.

Narrow : Narrow Clustered keys are preferred because of two main reasons, first Clustered key always goes to Non-clustered Index leaf level. If the Clustered key is very wide than it will increase the page size of all the non-clustered indexes on that table, more page size means more IO and more CPU cycles. Second, Wide Clustered Index will lead
to more intermediate pages, even clustered Index seeks has to travel using the intermediate pages to fetch the rows.

Unique : Clustered index has to be unique, if it is not unique then SQL Server adds a hidden 4 bytes integer with every row, and put this in every Non Clustered Index leaf level as well to link the non-clustered index to the clustered index, If making the cluster unique requires adding several wide columns, then it may be better to keep the index narrower and leave it as not unique.

Static : As above mentioned, the clustering key goes to the leaf level of all Non Clustered Index, if the clustering key changes, then update has to happen in all Non Clustered Index as well. And Clustered key had to move to the current page and this leads page fragmentation.

Ever Increasing : If the Clustered index key is ever increasing like numeric integer 1,2,3 etc... Then every row will insert at the end of the last key, so no page split happen and would be less prone to fragmentation. Hence, faster writes and reads.

Primary key is certainly unique and most of the time it is Narrow, static and ever increasing as well.
If DBA/Developer is not experienced than he can make a wrong decision in choosing the Clustering key, and this will lead more IO and CPU cycles. That's probably why Microsoft has decided to make a clustered index on the primary key by default. And this can certainly changeable depending on the requirement of the table.








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

Posted by: Iqbalionaccount on: 5/22/2017 [Member] Starter | Points: 25

Up
0
Down
As previously listed, the clustering key goes to the leaf level of all Non Clustered Index, if the clustering key changes, then update has to take place in all Non Clustered Index as well. And Clustered key had to go on to the current page and this leads page fragmentation.

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

Login to post response