Fabricate entities with multiple IDENTITY PKs?

Question

Disclosure: I'm a 'natural key' advocate myself and averse to the IDENTITY PK approach. But I do have a 'live and let live' approach to lifestyle choices, so no religious arguments here please :)

I have inherited a table where the only key is the IDENTITY PK column; let's call it ID. There are a many tables that reference ID. The intended process of creating a new entity seems to be:

1. INSERT INTO the table.
2. Use scope_identity to grab the auto-generated ID.
3. Use the auto-generated ID to INSERT into related tables.

In fact, there is a helper stored proc to create an entity and return the ID. However, I have a couple of issues:

I need to go further than the helper stored proc and create rows in related tables which themselves have IDENTITY PKs, so for each entity I need to grab several auto-generated values along the way. I need to fabricate several hundred entities and the helper procs are coded to handle one entity at a time.

What is the best way to bulk fabricate entities using the 'IDENTITY PK' design?

When using my own 'natural key' designs, I can generate the key values in advance, therefore it's simply a case of loading some scratch tables and INSERTing into the tables in the order expected by the foreign keys. Therefore, I'm tempted to find a sequence of high value INTEGER values (to match the type of the IDENTIY columns) which I know isn't being used now and hope that they won't be being used when the time comes to do the INSERT. Is this a good idea?

Answer 1

Are you talking specifically about MS SQL Server?

It is unfortunate that IDENTITY columns disallow explicit inserts by default. In other DBMSs, being auto-increment wouldn't stop you from inserting an explicit value into that column, which would make it easy to choose the keys in advance. Unfortunately on SQL Server you have the inconvenience of SET IDENTITY_INSERT to worry about.

there is a helper stored proc to create an entity and return the ID.

It seems a little over-the-top to me to use an sproc for that, since it's generally as simple as selecting the SCOPE_IDENTITY(). Quite often you can avoid the explicit select by writing each insert such that it can use the last insert's SCOPE_IDENTITY() directly.

find a sequence of high value INTEGER values which I know isn't being used now and hope that they won't be being used [...] Is this a good idea?

They don't necessarily have to be very high values; in fact if you did that often you'd be making many huge gaps in the IDENTITY values, which is generally better avoided. You could even use the MAX(column)+1 values as long as you either caught the error where someone else used those values in between times, or, better, do a select-max then insert in a transaction.