Surrogate Keys
-
I thought I'd open up a philosophical discussion on the merits of using Surrogate Keys (IDENTITY columns) in PRIMARY/FOREIGN KEY relationships. I understand that there are two schools of thought on this: some people are almost religiously opposed to them, while others swear by them.
I prefer surrogate keys over natural keys for a couple of reasons:
(1) The structure and content of natural keys can change over time; surrogate keys are immutable.
(2) Surrogate keys improve performance. INT data joins faster than CHAR data. INTs are smaller, so if your IDENTITY column has a CLUSTERED index on it, each page of each NONCLUSTERED index contains more rows. You can use a surrogate key in place of a composite key to further improve join performance.
I disagree with the assertion that surrogate keys are pointers. They are not. They are simply an additional candidate key that is autogenerated by the system and hidden from users.
Brian
MCDBA, MCSE+I, Master CNE -
Why do you wanna start a holly was on a beautifull Friday afternoon?
I'm not gonna be a part of this.
For now
. -
I agree. I designed a database which used policy numbers as primary keys which then cascaded downwards to a number of owning relationships. This was designed this way after being told by the business that these policy numbers would never ever ever change. After the system was in production for about a month guess what.......a policy number changed. This continued to happen for the next 3 years about 4 or 5 times per month. Each policy number change would require a manual set of inserts and deletes to be written to change the policy number in descending, then ascending order.
I have designed several other databases since then and have always used an identity field as a surrogate key. I have never run into any issues and would suggest this approach to everyone. I have, as you stated run into those who are soundly against the practice. The funny thing is most of their arguments come from text books on design methodology which are usually over 20 years old. Embrace the future of Surrogate Keys, don't fight it.
-
Instead of starting this discussion over, you should search http://www.google.com for "surrogate key" and have a lively argument with yourselves.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply