Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a table that's a working copy subset of a base table. The base table has a composite key with a clustered index. However I need a single column unique identifier for my framework to work with the table. Both keys are completely unique, and I'll need to make a clustered, unique index on the original, composite key.

Should I keep the composite key as a clustered primary key, or should I use the single column identifier as a non-clustered primary key? Though my framework requires a single identifying column I don't actually have to specify it as the primary key in the DB layer, so I'm not sure which is preferable.

I'm using SQL Server 2005 and the single column identifier will be an IDENTITY.

I'm assuming you will never use the IDENTITY as a FK?
–
JNK♦Feb 10 '12 at 17:45

@JNK No, it's syntactic sugar for my framework. All queries are done based on the composite key because the IDENTITY doesn't exist in my source table. So far as I've designed, the IDENTITY is never used except to update rows via the framework. Queries make extensive use of the composite key though, hence the clustered index to match the source table.
–
Ben BrockaFeb 10 '12 at 17:54

Then I can't think of any reason to even index the id field unless you select it, much less add the overhead of a constraint on it.
–
JNK♦Feb 10 '12 at 17:56

Since it's an ID you know it will be unique unless you put explicit values into it, which takes some effort. If you never JOIN or query it there's no point in doing anything else to that field.
–
JNK♦Feb 10 '12 at 17:59

@JNK True...none of that helps updates does it. I shouldn't ever need to select on it
–
Ben BrockaFeb 10 '12 at 17:59

2 Answers
2

This seriously depends on the expected half-life of your project, on how agile your environment is and so on. If your project is there to last, and changes are possible, I would not assume that your composite PK is never going to change. I have seen too many projects burned by such assumptions.

One good example would be the use of Social Security Number. Long ago it was a common practice to use it as a natural PK. Later on, many systems had to restrict its use for privacy reasons, and it became not possible to propagate SS#s all over child tables.

The systems that used identities adjusted to the change easily. The systems that used SS#s in child tables had to go through a major overhaul.

So, in many cases it is cheaper to use identities as PKs, adding a UNIQUE constraint on what currently is considered to be unique, and referring child tables to ParentID. This way we have to do less work to adjust when the situation changes.

I whole-heartedly agree. It's much easier and simpler to use surrogate keys (ie, identities) than to use composite keys.
–
DForck42Feb 10 '12 at 22:01

I think you may have misunderstood the question. They have already decided to add a second simple key (though not for the reasons your suggest) and that it will not be the clustered index. They are not asking whether they should use the simple in foreign keys. Rather, they are asking which of the two keys to make 'primary'.
–
onedaywhenFeb 13 '12 at 12:24

Assuming you set attributes 'clustered' and 'not null' explicitly and independently (rather than using 'primary' to imply them) then 'primary' is merely a designation. The choice of which candidate key to award this designation to is arbitrary.

It sounds to me that you have no objective criteria on which to base the choice.

Of course, folk do apply subjective criteria. Some will always favour the surrogate key (if one exists). Some will favour the key (most) used to reference the table in foreign keys. Some will insist that every table must have a primary key. However, all such actions are done merely for psychological reasons (e.g. following convention or what their college tutor taught them) which is often important only to them.