SQL SERVER – T-SQL Script to Add Clustered Primary Key

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]
ALTER TABLE [Sales].[Individual]
ADD CONSTRAINT [PK_Individual_CustomerID]
[CustomerID] ASC

Reference : Pinal Dave (http://blog.SQLAuthority.com)

14 thoughts on “SQL SERVER – T-SQL Script to Add Clustered Primary Key

    • 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?


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


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


  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s