如何停止主键标识列跳绳遗漏值 [英] How to stop Primary key Identity column skipping missing values

问题描述

我有三列 ID，姓名，职衔其中列 ID 是一个主键和一个表及其标识规范由1启用，它的种子。

I have a table with three columns ID, Name, Designation where column ID is a primary key and its Identity specification is enabled and it seeds by 1.

假如我有表中的10行。在 ID的序列值列顺序

Suppose I have 10 rows in the table. In a sequence values of ID column is in sequence

1,2,3......10. 

和用户删除再排10号插入新行我插入将有11 ID 列的值。我怎么能做出递增最后一行的价值？我的意思是我想要的10一样行新行的值之前将其列 ID 值9的。

and user deletes 10th row then inserts a new row I am inserting will be having 11 as the value of ID column. How can I make to to increment last row value? I mean i want "10" as the value of new row as row before it will be having column ID value as 9.

我不希望使用SQL Server的 MAX 功能，我希望它是自动递增。有什么办法来实现这一目标？

I don't want to use MAX function of SQL Server, I want it to be auto incremented. Is there any way to achieve this?

P.S：我使用实体框架插入DB值

推荐答案

有关的男人，还是女人，也喜欢住危险是的可能，虽然不明智以补种标识列。

For the man, or woman, that likes live dangerously it is possible, though ill advised to reseed the identity column.

该TSQL是

dbcc checkident ([tableName], reseed, [previousHighestID])

假设表格如下：

create table x ( a int identity(1,1), y varchar(max) )

实体框架：

using (var context = new XContext())
{
    context.Database.SqlCommand("insert into x select 'abc'");//id 1
    context.Database.SqlCommand("insert into x select 'cba'");//id 2
    context.Database.SqlCommand("delete from x where a = 2");//delete id 2
    context.Database.SqlCommand("declare @identMax int; select @identMax = max(a) from x; dbcc checkident (x, reseed, @identMax)");
    //reseed identity to 1 ( so next insert will be id 2 )
    context.Database.SqlCommand("insert into x select 'def'");//id 2 (again!)
    var X = context.X.SqlQuery("select * from x").ToList();    
}

真的不担心几个缺失值。一个int或BIGINT列可以重新present很多价值观。

Really don't worry about a few missing values. An int, or bigint column can represent a lot of values.

我想起杰克手持深思的：

I'm reminded of a Jack Handy deep thought:

如果你把你的钥匙进入熔岩只是让'时间去'的事业，男人，
   他们走了。

"If you drop your keys into molten lava just let 'em go 'cause, man, they're gone."

这篇关于如何停止主键标识列跳绳遗漏值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！