Post reply

Intelligent vs. Surrogate keys

  • October 16, 2003 at 6:05 am

    #478434

    quote:

    A clustered index on an identity column can be a problem if page level locking is used. Every insert will likely be into the same page.

    I would not waste the clustered index on the identity. I look for the most used query with an "order by" that returns several records. The order by columns is a good candidate for a clustered index.

    Hopefully you don't get page level locking too often or something else is wrong. As in most database design questions you have to weigh inserts versus queries versus data integrity. If you are OK with page splitting and do range queries to return a lot of columns, do as Rstone says. If you are heavy inserts and don't want pagesplits, clustered index on ID is good for that. And for DW, I would always use surrogate keys.

    Frank, thanks for the semantics help, I think most people realize that IDENTITY is not a data type (as you have to specify INT in creation). 🙂

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

Viewing post 16 (of 15 total)

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