When I was building software as a full time job, the choices for automatically generating surrogate keys were the identity property and the NEWID() function. At that time, GUIDs were too cumbersome to deal with, and I often used identity values in tables. I looked for natural keys, but often these were secondary keys for me and I preferred to use numerical values in many tables as PKs and FKs.
In the last few years I’ve run into a few customers that prefer sequences, which are a separate object in your SQL Server database. There are some challenges with sharded systems and using deployment technologies like SQL Compare, which is one reason I’ve avoided them. However, I was reading Dr. Greg Low’s blog recently where he noted that he prefers sequences to identities. He gives a good comparison of the reasons why sequences can be easier to manipulate, with some advantages because these are a separate object. I think he makes a good case why we might always want to consider sequences over identity values in new development.
He’s convinced me, though now I need to learn a new habit and build new skills to quickly and easily develop sequences for table keys instead of relying on identity values. This is a big change for me, with nearly 30 years of writing identity properties in CREATE TABLE statements.
Learning to adopt new techniques and changing habits of the ways that we grow and change, and certainly how we build better software. That’s a tenet of DevOps. Experiment and learn. This is a place where I’ll start to grow and see what I think as I build demos and PoCs for customers, giving sequences a chance. I wonder how many of you might rethink using identities in the future as well.
Steve Jones
Listen to the podcast at Libsyn, Stitcher or iTunes.
Voice of the DBA 