披露：我自己是“自然钥匙”的倡导者，反对身份 PK 方法。但我确实对生活方式的选择采取“互不干涉”的态度，所以这里请不要有宗教争论:)
我继承了一个表，其中唯一的键是 IDENTITY PK 列；我们称之为 ID。有很多引用 ID 的表。创建新实体的预期过程似乎是：
- INSERT INTO 表。
- 使用scope_identity来获取
自动生成的 ID。
- 使用自动生成的 ID 进行 INSERT
到相关表中。
事实上，有一个辅助存储过程来创建实体并返回 ID。但是，我有几个问题：
我需要比帮助程序存储过程更进一步，并在相关表中创建行，这些表本身具有 IDENTITY PK，因此对于每个实体，我需要沿途获取几个自动生成的值。
我需要制造数百个实体，并且辅助过程被编码为一次处理一个实体。
使用“IDENTITY PK”设计批量制造实体的最佳方法是什么？
当使用我自己的“自然键”设计时，我可以提前生成键值，因此只需加载一些临时表并按照外键预期的顺序插入到表中即可。因此，我很想找到一系列高值 INTEGER 值（以匹配 IDENTIY 列的类型），我知道这些值现在没有被使用，并希望在需要时它们不会被使用插入。这是个好主意吗？
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:
- INSERT INTO the table.
- Use scope_identity to grab the
auto-generated ID.
- 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?
发布评论
评论（1）
您是专门谈论 MS SQL Server 吗？
不幸的是，IDENTITY 列默认不允许显式插入。在其他 DBMS 中，自动增量不会阻止您向该列中插入显式值，这将使提前选择键变得容易。不幸的是，在 SQL Server 上，您需要担心 SET IDENTITY_INSERT 带来的不便。
对我来说，使用存储过程似乎有点过分了，因为它通常就像选择 SCOPE_IDENTITY()。通常，您可以通过编写每个插入来避免显式选择，以便它可以直接使用最后一个插入的 SCOPE_IDENTITY() 。
它们的值不一定非常高；事实上，如果您经常这样做，您会在 IDENTITY 值中产生许多巨大的差距，这通常是最好避免的。您甚至可以使用 MAX(column)+1 值，只要您捕获到其他人在两次之间使用这些值的错误，或者更好的是，执行 select-max 然后插入交易。
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.
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.
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)+1values 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.