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.
Best Answer
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.