Post reply

Why not use PK with Clustered Index instead of PK with Unique Nonclustered Index

  • February 12, 2011 at 6:49 pm

    #231817

    I ran into this while playing around and asked myself why was it designed like this. Educate me 🙂

  • February 14, 2011 at 10:56 am

    #1286612

    This is a just-so story that I'm coming up with, but it sounds like it makes sense, at least to me... 🙂

    Clustered indexes are, in most cases, a Good Thing. Having a guaranteed unique clustered index is a good thing as well, as then SQL Server doesn't need to add a uniqueifier (Michelle Ufford has an article which describes this[/url]; check the Unique header). Primary keys are guaranteed to be unique, so if the person just takes default settings, it's nice and easy to create the resulting index as a unique clustered index. That way, the user gets a clustered index and the benefits it provides over a heap, and SQL Server gets a guaranteed unique value for the clustered index.

    Similarly, I would imagine that the reason why unique key constraints create unique non-clustered indexes by default is that if you're going to have a UKC, you probably already have a PK and thus probably already have a clustered index.

    You can set up a primary key to use a unique non-clustered index, though, in the event that your primary key and clustered index don't match. We do that in some cases where the developers want GUIDs for their primary keys but I want numeric values for the clustered index to limit page splits.

  • February 27, 2011 at 4:30 pm

    #1292089

    Kevin Feasel (2/14/2011)

    This is a just-so story that I'm coming up with, but it sounds like it makes sense, at least to me... 🙂

    I don't know about it being a "just-so story"... seems like a pretty damned good story especially for your first post. Well done!. 🙂

    ...numeric values for the clustered index to limit page splits.

    Or a date value that represents the date inserted.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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