difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Natural Key should always be prefered. Assuming that OrderId is the ID which is printed or somewhere used in your order process, this represents the natural key. CustorderID is a artificial key which is not used any further in your process and just there to identify the unique row.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

From application point of view - it's a lot easier to use an Id column for almost any operation. It allows you to use natural comparing and serializing (ToString) rather than the need to serialize the key somehow, and implement HashCode (which many implement badly) thus simplifying the code, thus, again, increasing maintainability.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.