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] GO
ALTER TABLE [Sales].[Individual] ADD CONSTRAINT [PK_Individual_CustomerID] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Constraint and Keys, SQL Scripts
Previous Post
SQL SERVER – UDF vs. Stored Procedures and Having vs. WHERE
Next Post
SQL SERVER – Pre-Code Review Tips – Tips For Enforcing Coding Standards

Related Posts

14 Comments. Leave new

  • Wow. Whoever needs this information shouldn’t be allowed near a database.

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

      Reply
  • Gaurab Chatterjee
    September 4, 2009 12:25 pm

    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.

    Reply
  • @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.

    Reply
  • What is the difference between above query and CREATE CLUSTERED INDEX …. ON tabaleName (….) . Which way to use in between?

    Reply
  • Which is the difference between a clustered or a non clustered primary key ?

    Reply
  • How can i create to primary key in a table ?

    Reply
  • 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…

    Reply
  • YrthWyndAndFyre
    May 3, 2013 7:35 pm

    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.

    Reply
  • sanjay chaudhari
    February 6, 2014 5:23 pm

    Hi Painal,
    why primary key creates clustered index in sql server 2008 ?

    Reply
  • sanjay chaudhari
    February 6, 2014 5:24 pm

    why unique key creates non clustered index in sql server 2008 ?

    Reply
  • How to alter this primary create cluster index – without dropping

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

    Reply

Leave a Reply