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
)
What to Check Before You Add a Clustered Primary Key
The script is short, but on a real table it can fail or cause trouble. Here is what I check first:
- Is there already a clustered index? A table can have only one. If one exists, drop it first or create the key as
NONCLUSTERED. - Are the key columns
NOT NULL? Adding a key on a nullable column fails, so change the column first. - Are the values unique? Run a
GROUP BYon the key column withHAVING COUNT(*) > 1to find duplicates before theALTER TABLEfinds them for you. - How big is the table? Building a clustered index rewrites the whole table, which takes time and can block other users. Plan it for a quiet window.
Choosing the key matters as much as creating it. The clustering key is stored in every nonclustered index on the table, so a wide key makes all of them bigger. A good key is narrow, unique, does not change, and ideally grows with each insert. An INT or BIGINT identity column fits all four.
Try to avoid a random GUID as the clustering key. New rows land all over the table instead of at the end, which causes page splits and fragmentation. If you need a GUID, NEWSEQUENTIALID() as a column default reduces that problem, or you can keep the GUID in a nonclustered index.
After you run the script, confirm the result with sp_helpindex and the table name, or look at sys.indexes, where the clustered index has index_id 1. Also give the constraint a clear name, as this script does with PK_Individual_CustomerID, so it is easy to find later.
If the script fails, read the error message carefully. It tells you whether duplicate values or a nullable column blocked the change, which points you straight to the fix.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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 ?