Hi,

I use surrogate keys in all tables (except a few static metadata tables) in SQL Server, and in VFP since whe have autoincrement field.

Performances are dramatically increased, especially if you build a clustered index on it, and add Integer column for FK in related tables :

• data stays physically in that index, so you read lines of the child table when the JOIN is running, and so your query involves less I/O on disk.
• Index size decreases, both on PK and FK.
• you don't need any cascade on update (declarative or by trigger), as these keys are never updated!

I don't see any trouble in debugging, on the contrary it seems easier for me:
RI errors involve only PK and FK, and Business rules errors never include PK or FK. The same rule is valid on writing SP or UDF.

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