RE: Identity columns, was: Using long names

<Rob>
You know, if there weren't actually people out there changing primary keys we wouldn't be having this discussion on surrogate keys. There are some really "retentive" people out there who hate gaps in sequence numbers. "Why is there an order 501 and 503 but no 502? That's a SOX violation!" (If it is, show me exactly where that is in the regulations.)

Dieter brings up some practical examples. Like merging companies and they both have a customer 1. His solution was to prefix the newly merged customer number with a 1, ie it might become 1000001. I don't see how a surrogate key makes this any easier as the two companies could have matching surrogate keys and one would also have to change that.
</Rob>

Having a Requirement to have Order Numbers without gaps, is quite another animal. Here you would need a function to get a free number (with filling gaps). If the numbers have to be in sequential incoming order and without gaps, you would have to serialize the complete order process, including drawing the number (commitment controll could do main of the work).

Merging two companies the clean solution in a well normalized database without surrogate keys would be to add a column company and add this column to the key. Using a surrogate key you would need a column company too, but the key would stay unchanged, only a unique condition would be changed.

Having a database maintained column identifier as surrogate key it might happen that both companies could add a record (on diffrent systems) having the same generated record number and the data couldn't be merged together. This could be solved by globally unique identifiers or by adding the system name as addditional colum to thr database maintained column identifier (having a primary key of two columns).

D*B