I had someone ask me recently about whether or not there was a place where a table did not need a primary key. Apparently it had come up in an interview and this person was interested in whether or not a valid reason existed for not putting a primary key on a table.
So I'll ask you to think about it and I would be very interested to know where or where not this would make sense. I was asked this about a real, permanent table, and not a throwaway table used for just a batch or a temporary table. A table that exists permanently in your database, whether or not anyone is connected to the server.
I've been working with SQL Server for over a decade and a half and in that time, I've heard constantly that the optimizer prefers to have an ordered table and not a heap. So in that sense, every table should have at least one index, and it should be clustered. The exact reasons behind this don't seem to be clear to me, or perhaps I've never really had a good reason other than to follow advice from people at MS and the Inside SQL Server series (v6.5, v7.0, v2000, v2005 - v1, v2005 - v2, v2005 - 3, and v2005 - 4). I'd like to think that's the case, but I certainly have trouble articulating why a PK makes sense to the optimizer. There's not shortage of recommendations from MSDN, Technet, and other sites like SQLServerCentral.com that recommend this, but I've not seen many details on exactly why.
However Primary Keys don't necessarily have anything to do with clustered indexes, and I'm getting a little distracted here. If we consider the original question, does it make sense to have a table without a primary key?
I have to say that I have done it in a few places. There was a case where we needed to build a report every day for an inventory system and we wanted to build this once a day and it would be for shared querying by multiple users. Essentially we had a few set columns that corresponded to a report and we populated them in a batch fashion with data that people would use for individual reports throughout the day. We did this to save on the expensive calculations involved in building the report and to be sure that Purchasing Officer A saw the same information as Manager B on that day. It definitely helped prevent a lot of calls to my office.
Now the table was indexed on some fields, but there wasn't a PK defined. Essentially we had no way to get to an individual row and in this day and time I might build a series of SSRS reports that were cached for the day instead of using this table, but back in 1996, this made sense.
I've also used report tables in which we were building a report for display purposes. We had a shared DBA report that rolled up data from individual servers and we just batched data from each server with the server name into the table, a series of rows that listed information about the server in report format. This wasn't data that would be edited and we included the time the row was written, so maybe that was a PK, even though it wasn't explicitly defined as such.
Those were the only two examples I've come up with, though in those cases I still had indexing, even clustered indexing, just not a specific PK defined because we had problems determining what that would be. Today I'd probably define a PK, but I'm not 100% positive since it's possible that we might allow two rows with the same data in them for some business reason.
My general feeling, meaning that I would take it as a rule without a very good reason not to, is that every table should have a primary key, even a surrogate one. However I'd be welcome to listening to other cases where you might not want one.
Steve Jones
The Voice of the DBA
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. You can get feeds from there.
I've had some issues with my old laptop, awaiting a new one this week, so I've had trouble capturing audio for now separately from video. Look for the audio podcasts to reappear soon.
Today's podcast features music by the beautiful, jazzy, Robin Stine. Check her music out at www.robinstine.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.
