Jr. DBA asked me three times in a day, how to create Clustered Primary Key. I gave him following sample example. That was the last time he asked “How to create Clustered Primary Key to table?”
USE [AdventureWorks]
GO
ALTER TABLE [Sales].[Individual]
ADD CONSTRAINT [PK_Individual_CustomerID]
PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)
Reference : Pinal Dave (https://blog.sqlauthority.com)
SQL SERVER – T-SQL Script to Add Clustered Primary Key
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
14 Comments. Leave new
Wow. Whoever needs this information shouldn’t be allowed near a database.
What a remarkably ignorant comment to make. PD did say he was a JUNIOR. I suppose learner drivers shouldn’t be allowed on the roads should they?
If i am not wrong please clear me .
I know that if you create primary key in a table it uses a clustered index default,
So why we need to create Clustered primary Key ?
I am bit confused about this matter please help me out.
@Gaurab Chatterjee
You don’t need it. It is there so the code can be explicit, like any default. This might be helpful when CREATEing other TABLEs as well where the PK is not CLUSTERED.
What is the difference between above query and CREATE CLUSTERED INDEX …. ON tabaleName (….) . Which way to use in between?
Which is the difference between a clustered or a non clustered primary key ?
How can i create to primary key in a table ?
If thats what he asked then he aint a dba or even a computer literate fot that matter. You have the time to answer that three times…
A primary key is a key that is guaranteed to have a unique value for every row in the table. The difference between clustered and non-clustered is that if the index is clustered, the records are actually *in that order*, whereas non-clustered is a separate index that references the actual data, so the order is provided indirectly without actually moving any records around.
Thus, inserting data into a clustered index may involve shifting rows around. The ‘FillFactor’ is employed to indicate how much empty space to leave on each page precisely for this reason, but inserts may occasionally still incur the overhead of a ‘page split’. Commonly a clustered primary key is placed on an INT identity field, in which case this is not an issue as every new inserted row is always added at the end of the index. However, if your primary key is a GUID, then page splitting could be an issue and this should then be factored into your design. Clustered indices are more space-efficient and faster than non-clustered indices, but there is a price for everything.
An ordinary index is always a separate object, may be unique or non-unique, and may be created and destroyed independently of the table it refers to. A primary key index is always unique by definition, may not be anything else, is always part of the table definition and must be created or destroyed with the ALTER TABLE statement or created as part of the CREATE TABLE statement.
And Ram, if you were born knowing everything about databases, then you can hold your nose in the air. If you were not, then you were once as ignorant, and had to be taught, so don’t be such a hypocrite. Help out the newbies.
That’s a well descriptive answer, to both SQL Q n one wing flyer Ram!
Hi Painal,
why primary key creates clustered index in sql server 2008 ?
why unique key creates non clustered index in sql server 2008 ?
How to alter this primary create cluster index – without dropping
When adding a primary key to an existing table with index, what will hapen with those existing index ? Do we have to rebuild them in order to consider the PK created ?