Hi,

Shall anyone share his experience on using surrogate key in database design? I know that surrogate key is not extensible, but I found that is is very troublesome to do debugging. Beside, we always required to join multiple tables in order get some "meaningful" result. It could cause performance issue as well. Also, maintenance by technical support also not ease.



However, I see most of the vendor using surrogate key.



Anyone can share?

Thank you

Hi Chan,

On balance surragate keys are likely to give better performance than complex natural PK's and are always easier to maintain and use.

Our main transaction table at one site has about 9 million rows and 17 Fk's. I've never wanted to return transaction details and data from all 17 FK's in one query but I will often extract data from 8 to 10 FK relationships for reporting purposes.

I've never seen any perfomance problems (SQL Server 2000 / W2K3 Standard / 4Gb Menory / Dual Xeon Processors (machine about 3 years old). I would expect a query that returns 10,000 records to run in a few seconds. However, that being said we always return data within a date range and the transaction table has a clustered index on date so getting at the transaction information itself with always by very quick. This is were your performance effort needs to go - getting at the transacion data quickly - SQL will always do a good job of associating the related FK info.

Cheers, Peter

Thanks for the posts, guys!  Good stuff out here!