Post reply

Clustered Index need Primary keys?

  • June 7, 2004 at 9:49 am

    #108353

    This is probaby a basic question. I want to drop a primary key from a table and basically add a clustered index (with the UNIQUE option applied) that would take its place. This is basically providing the same unique contraint function. Right? There is no reason to add primary keys to a unique clustered index, right? Our analysts always require a primary key. However, I don't really need a primary key in this case?

    Specifics on the table: The table currently has a single primary key defined on columns 1, 2, and 3 (date,time,seq; an the date and time are CHAR data types! but don't go there for now because this can't change anytime soon). There are no indexes on the table; that primary key is it. I want to drop the primary key and create a clustered index (with the UNIQUE option) on columns 1,2,3.

    Wouldn't the clustered index be more efficient, since less data would have to be read into memory (reduce paging). Thanks for any advice!


    smv929

  • June 7, 2004 at 11:47 pm

    #509381

    By default, a primary key is supported by a unique clustered index, so by dropping the key and creating the index you won't be gaining anything.

    You will gain if the key is nonclustered (check in enterprise manager. Right click table-> manage indexes) If so, recreate the key as clustered, but you will gain nothing from dropping the key if it is already clustered.

    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 2 posts - 1 through 1 (of 1 total)

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