Sql server身份列问题 [英] Sql server identity column problem

问题描述

我有一个具有标识列名称colorid的表。我没有删除任何行，但colorid自动从5更改为10050，如下面的exmpale。是什么原因。

colorid	代码	名称
1	Col-0000001	红色
2	Col-0000002	蓝色
3	Col-0000003	橙色
4	Col-0000004	绿色
5	Col-0000005	白色
10050	Col-0000006	黑色
10051	Col-0000007	黄色

I have a table which have a identity column name colorid.I didn't delete any row but colorid automatically changed from 5 to 10050 like following exmpale. What is the reason of it.

colorid	code	name
1	Col-0000001	Red
2	Col-0000002	Blue
3	Col-0000003	Orange
4	Col-0000004	Green
5	Col-0000005	White
10050	Col-0000006	Black
10051	Col-0000007	Yellow

推荐答案

最可能的原因是记录从6到10049包括删除 - IDENTITY值不是回收。因此，如果你删除行，他们使用的数字将不会被重复用于下一行，即使它是表中的最后一个数字。

所以如果你有三行：

The most likely reason is that records from 6 to 10049 inclusive were deleted - IDENTITY values are not "recycled". So if you delete rows, the numbers they used will not be reused for the next row, even if it was the last number in the table.
So if you had three rows:

1
2
3

你删除了第2行，然后又增加了一个：

And you deleted row 2, then added one more:

1
3
4

如果您有三行

If instead you had three rows

1
2
3

你删除了所有，然后又添加了三个，你仍然会得到：

And you deleted them all, then add three more, you will still get:

4
5
6

其中一个原因是其他表可能在没有正式外键关系的情况下使用ID值，重用意味着旧的相关数据最终会结束如果使用价值回收，则应用于新行。



您可以在不使用IDENTITY值的情况下获得序列号：

One of teh reasons for this is that other tables may use the ID values without a formal foreign key relationship, and reuse would mean that the "old" related data would end up applied to the new rows if value recycling was used.

You can get sequential numbers without using the IDENTITY values:

SELECT ROW_NUMBER() OVER (ORDER BY colorId) AS Row, colorId, code, [name] FROM MyTable

这篇关于Sql server身份列问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！