This editorial was originally published on Mar 5, 2008. It is being re-run as Steve is on vacation.

The Identity property might be one of the most controversial constructs in SQL Server, or at least right up there with the NULL/No NULLs debate. Someone sent me a note awhile back about this debate and I thought it might be worth bringing forward in an editorial.

When I was getting started in SQL Server in the early 90s, I learned about primary keys, natural keys, and the ways you go about modeling a system. It all made good sense, especially at the time when I was working with systems that took years to build and were based on very well understood processes with tight controls on the data involved. So I learned to look for natural keys.

A few years later I moved to a smaller company, considerably more agile, that was trying out new way of doing business. In the process of trying to revamp their systems, I found that things I was told were "unique" or "set in stone", weren't. Not even close, more like set in Play-Doh. And even then in a very humid environment with no danger of things drying out.

I ran into identity field at some point and found that they were lightweight, solved a lot of problems, didn't interfere with natural keys, and above all, were easy for a DBA to remember and work with when building applications or troubleshooting issues.

However there is often a huge debate out there on what the value of identity colums is and how should they be used. Some people, like Joe Celko, rail against their use in a relational system. Others use them everywhere, making them clustered primary keys for every table. Many people are somewhere in between, using them where it fits, and sticking with natural keys if they are identifiable. Some people even use identities as their primary key, relegating natural keys as alternates with a separate unique index.

I don't think we put out enough information here, or on any other database site, that would help people better model their data and build relational systems. It's a hard topic to write about and it's hard to do it well without making everyone's eyes glaze over.

But I wanted to put this out there and see what you think about identity columns and their use (or mis-use) in today's databases. They are definitely a tool that's available to the SQL Server professional and one that I bet everyone has an opinion on. We surely value those opinions and as with many of these debates, I'm looking forward to learning a thing or two.

Steve Jones

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

• Windows Media Podcast - 27.8MB WMV
• iPod Video Podcast - 22.2MB MP4
• MP3 Audio Podcast - 4.6MB

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.