Some thoughts on auto-incrementing IDs

Jan 10, 2014
Lincoln Bovee'
  • The implementation & syntax feels like an afterthought, like the engineers of SQL Server made something self-consistent, and then the market research wing forced an intern to tack on the identity feature.
  • In terms of “separation of responsibility”, I feel this is the role of code, not the DB. The DB shortcut is used because it’s “easy”. (I know this is subjective.)
  • It feels like 20% of DB admin type tasks (not developer tasks) are made harder when tables have identity columns. The pain is in the wrong place. (This is based a little on some limited personal experience.)
  • auto-incrementing DB fields are just plain evil. (retracted)

    • The rest is more concrete, and boils down to the alternatives being relatively easy.

    1. Live with the race condition of (get next ID), then (insert record). (Not actually a problem, surprisingly often.)
    2. Wrap (get next ID), (insert record) in a DB transaction. (Transactions are what DBs are good at.)
    3. Get ID from an external sequence that never gives the same ID back (usp_intranet_getNextSequenceID)
    4. Another elegant option that’s even easier, and also eliminates the race condition:

    This will return a record with the ID value you inserted, and because it’s one “line” of SQL, it all happens in an implicit transaction.

    Auto-incrementing IDs

    These options, particularly #4, are not difficult. It also leaves the “magic” (if you can call it that) right out in the open, where everyone can see it. I’ve used #4 now various times, and is my new favorite option.

    Views – 2007

    Leave a Reply