clustered index and pK

  • 😎

    Hi,

    when we create a primary key on a table clustered index is created automatically,

    can u diff between primary key and clustered index

  • Hi,

    A primary key is a constraint, while a clustered index is an index.

    A primary key makes sure that duplicate records are not inserted into the table. It provides entity integrity to make sure each record is unique.

    A clustered index creates a separate structure within the database, which will be used for optimization of your queries. A clustered index also physically sorts the records of the table based on the column(s) it is defined upon.

    When a primary key is created on a table along with a clustered index; all the records would be physically sorted and uniqueness guaranteed.

    It is also possible to create a table with a non-clustered index on the primary key instead of a clustered index.

    Hope this was clearly put out

    Regards

  • They are unrelated terms.

    A primary key is the identifier of the row. It must be unique, it must have no nulls and it's used to enforce the entity integrity of the table. It's also used in foreign key relationships.

    A clustered index is an index that has the data pages as the leaf level of the index. It enforces the storage order of the rows and pages in the table

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In fact, there are certain conditions where your Primary Key most certainly should not be considered for your clustered index. If, for example, you are designing a backend to a web (or other) app which is using a GUID. As a GUID is (generally - there are various types) non-monotonic (or random, if you prefer) this means that new records being inserted into the table - if this is your clustered index, rather than being inserted at the end of the table can end up being inserted pretty much anywhere. This could lead, potentially, to a lot of physical IO due to physically re-ordering your table, page splits, locking. Basically - certainly for anything other than pretty moderate volume - performance suicide

  • By default, the primary key is the clustered index. When you create the table, you can specify "nonclustered" on the PK row, which will override that default. If you do that, you can separately create your own clustered index on the table.

    Example:

    create table dbo.Table1 (

    ID int primary key NONCLUSTERED, -- keyword

    Col1 ... etc., add more columns

    go

    create clustered index CID_Table1_IndexName on dbo.Table1 (Col1)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Another very good reason for not setting the PK as a clustered index: some other column may be a better candidate for the clustered index, based on the type of queries performed on the table. If, for example, a column is used frequently in range queries or in the GROUP BY/ORDER BY clause, it is a perfect candidate for the clustered index.

    Examine your queries and do not commit too early to making something (especially the PK) the clustered idx. It may be difficult to change this later on.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • It true that when we define the primary key by default it create the clustered index, but its not true you can define it as non clustred index also.

    Primary Key uniquely defines each row in the table.

    Clustered index store the index ( like we see in book INDEX ) for this unqiue data on index pages.

    Regards,

    Abhijit More

  • Abhijit (7/15/2008)


    It true that when we define the primary key by default it create the clustered index, but its not true you can define it as non clustred index also.

    Really?

    CREATE TABLE IndexTesting (

    ID int IDENTITY,

    SomeChars Varchar(50),

    InsertDate DATETIME DEFAULT GETDATE()

    );

    GO

    ALTER TABLE IndexTesting

    ADD CONSTRAINT pk_Testing PRIMARY KEY NONCLUSTERED (ID)

    GO

    CREATE CLUSTERED INDEX idx_Testing_Date ON IndexTesting (InsertDate)

    The primary key is enforced by an index. It does not have to be a clustered index though.

    Primary Key uniquely defines each row in the table.

    Clustered index store the index ( like we see in book INDEX ) for this unqiue data on index pages.

    The point of the clustered index is that it is the table and stored the full data in the leaf pages of the clustered index, with a b-tree structure. It does not store the leaf pages separate for the table (like a book index). A better analogy for the clustered index is the pages of the book itself, with the page number as the clustering key.

    The clustered index does have to be unique, but if it is not specified as unique (which is allowed), SQL will make it unique behind the scenes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply