表中的自动增量ID值如何保持下一个值，即使删除了一行？ [英] How auto increment ID value in table maintains the next value even a row is deleted ?

问题描述

例如：如果我的表Employee具有列EmpId INT（IDENTITY（1,1），EmpName VARCHAR（60）

表在删除之前看起来像

- ------- -------

EmpId EmpName

1 xxx

2 yyy



表后删除第二行即EmpId = 2

--------- -------

EmpId EmpName

1 xxx



表插入新行后

-------- - -------

EmpId EmpName

1 xxx

3 zzz



这是我的问题

这个EmpId如何自动递增为3而不是2.是否有任何临时表来存储Identity列的最后一个值？

或者

它是如何获得的确定下一行的表格EmpId需要给出3？



我有什么尝试过：



我试过Google搜索，但结果与更改身份相关使用SQL SERVER中的Reseed

解决方案

不要。

你可以这样做 - 但它确实很危险，特别是在像SQL这样的多用户系统。

IDENTITY列应该为行提供唯一值，而不是提供没有间隙的序列号。如果您需要，请使用 ROW_NUMBER（Transact-SQL） [ ^ ] - 它提供了当前SELECT语句的序列。 / blockquote>

ID序列只是所讨论的数据库引擎所维护的表的属性，它不是自己保存在表中。如果你想改变这个数字，那么这个过程被称为重新播种，但是除非你真的需要，否则你不应该改变这个数字的原因有很多，你的身份证上有空白。 / BLOCKQUOTE>

Ex: If I have table Employee with column EmpId INT(IDENTITY(1,1),EmpName VARCHAR(60)
table looks like before delete
--------- -------
EmpId EmpName
1 xxx
2 yyy

table after delete second row i.e EmpId =2
--------- -------
EmpId EmpName
1 xxx

table after insert new row
--------- -------
EmpId EmpName
1 xxx
3 zzz

Here comes my question
How this EmpId is automatically incremented as 3 instead of 2. Is there any temporary table to store the last value of Identity column ?
Or
How it is getting Identified for the next row of the table's EmpId need to given as 3?

What I have tried:

I tried Google search but result comes out related to changing the identity value using "Reseed" in SQL SERVER

解决方案

Don't.
You can do it - but it's really dangerous, particularly in a multi user system like SQL.
IDENTITY columns are supposed to give a unique value to a row, not to provide a sequential number that has no gaps. If you want that, then use ROW_NUMBER (Transact-SQL)[^] instead - it provides a sequence for the current SELECT statment.

The ID sequence is simply a property of the table that the database engine in question maintains, it isn't held in a table on its own. If you want to alter this number then the process is known as "re-seeding" but there are many reasons why you shouldn't alter the number unless you really have to, it is fine to have "gaps" in your IDs.

这篇关于表中的自动增量ID值如何保持下一个值，即使删除了一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！