为什么我的“身份"列中有空白? [英] Why are there gaps in my IDENTITY column?
问题描述
如果我正确输入数据，我的表就可以正常工作-我可以插入数据，并且IDENTITY值为1、2、3、4.但是，如果我输入错误并收到错误消息，例如
My table works fine if I enter data correctly - I can insert data and the IDENTITY values are 1, 2, 3, 4. However, if I make a mistake and get an error message, e.g.
无法将值NULL插入表'table'的列'x'中；列不允许为空. INSERT失败.
该语句已终止.
Cannot insert the value NULL into column 'x', table 'table'; column does not allow nulls. INSERT fails.
The statement has been terminated.
然后，如果我成功插入另一行，则IDENTITY值为6，而不是5.
Then if I insert another row successfully, the IDENTITY value is 6, not 5.
我该如何解决这个问题?
How can I solve this problem?
推荐答案
首先，
这没问题.
这个问题"是非常有设计的.
The "problem" is very, very much by design.
请不要重复，不要指望IDENTITY列保持一组不错的，连续的值且没有间隙.您真的不应该在乎是否存在差距，但是差距可能是由各种原因引起的.删除，回滚，故障转移和服务重新启动，等等.停止担心该值并尝试防止出现差距； IDENTITY精确有效地工作是因为SQL Server不会执行防止间隙所需的所有额外工作.如果要这样做，则必须手动推出自己的解决方案.
Do not, I repeat, do not expect IDENTITY columns to maintain a nice, contiguous set of values, with no gaps. You really shouldn't care if there are gaps, but they can be caused by all kinds of things. Deletes, rollbacks, failovers and service restarts, etc. Stop worrying about the value and trying to prevent gaps; IDENTITY works efficiently precisely because SQL Server doesn't do all the extra work that would be required to prevent gaps. If you want to do that, you'll have to roll your own solution manually.
哦，补种不是一种选择.这就是为什么.首先，让我们以表没有主键(或至少主键不在IDENTITY列上)为例:
Oh, and reseeding is not an option. Here's why. First, let's take an example where the table has no primary key (or at least the primary key is not on the IDENTITY column):
CREATE TABLE dbo.foo(id INT IDENTITY(1,1));
GO
INSERT dbo.foo DEFAULT VALUES;
GO 5
DBCC CHECKIDENT('dbo.foo', reseed, 0);
INSERT dbo.foo DEFAULT VALUES;
GO
SELECT id FROM dbo.foo ORDER BY id;
GO
DROP TABLE dbo.foo;
结果:
id
----
1
1 <-- oops! Duplicate. We probably didn't want that, right?
2
3
4
5
现在，如果IDENTITY列也是主键(很常见):
Now, if the IDENTITY column is also the primary key (pretty common):
CREATE TABLE dbo.foo(id INT IDENTITY(1,1) PRIMARY KEY);
GO
INSERT dbo.foo DEFAULT VALUES;
GO 5
DBCC CHECKIDENT('dbo.foo', reseed, 0);
GO
INSERT dbo.foo DEFAULT VALUES;
GO
DROP TABLE dbo.foo;
糟糕:
2627消息，第14级，州1，第7行
违反了主键约束'PK_ foo _3213E83FE3F1E24C'.无法在对象"dbo.foo"中插入重复的密钥.重复的键值为(1).
该声明已终止.
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK_foo_3213E83FE3F1E24C'. Cannot insert duplicate key in object 'dbo.foo'. The duplicate key value is (1).
The statement has been terminated.
那么您打算在那里做什么?循环播放，直到您不再遇到异常?编写各种复杂的间隙和孤岛代码，找到第一个间隙，然后按SET IDENTITY_INSERT ON;并手动插入值?为什么?你得到了什么?将行军速度减慢至20亿次并产生溢流?我不理解这种对序号的迷恋，在IDENTITY列中没有空格.这是一个替代性的，毫无意义的价值.让我再重复一次:
So what do you propose to do there? Loop until you no longer get an exception? Write all kinds of convoluted gaps and islands code that finds the first gap, then SET IDENTITY_INSERT ON; and manually insert values? Why? What did you gain? Slow down the march to 2 billion and an overflow? I don't understand this obsession with sequential numbers and no gaps in an IDENTITY column. It's a surrogate, meaningless value. Let me repeat once more:
间隙不是问题.
问题不在于技术.首先，问题是令人担忧的.如果您在乎差距，请停止尝试找出如何使用身份来防止差距的方法.只需停止使用IDENTITY，即可.或者停止关心差距.
The problem isn't the technique. The problem is worrying about gaps in the first place. If you care about gaps, stop trying to figure out how to use IDENTITY to prevent them; just stop using IDENTITY, period. Or stop caring about gaps.
这篇关于为什么我的“身份"列中有空白?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
