自动生成ID .......并删除ID可重用..... [英] Auto Generate ID.......And Deleted ID Reusable.....

问题描述

我需要为我的ID字段使用自动生成编号....我已经使用[ID] [int] IDENTITY（1,1）NOT NULL，用于自动生成..但是当我尝试删除一些数据时在表中然后删除id不再使用........我需要再次使用该删除的ID ...我该怎么办？

I need to use auto generate number for my ID field....I am already use [ID] [int] IDENTITY(1,1) NOT NULL, for auto generate..But when i try to delete some datas in table then that deleted id not used again........I need to use that deleted id again ...How can i do that?

推荐答案

两种方法



1. DBCC CHECKIDENT（Transact-SQL） [ ^ ]

参考 - 删除行后重用身份值 [ ^ ]。

Two Methods

1. DBCC CHECKIDENT (Transact-SQL)[^]
Refer - Reuse identity value after deleting rows[^].

Quote：

您可以使用以下方法设置IDENTITY值：

You can use the following to set the IDENTITY value:

DBCC CHECKIDENT (orders, RESEED, 999)

这意味着您必须运行基于该语句在每个DELETE上。这应该开始强调为什么这是一个坏主意...



数据库不关心顺序值 - 仅用于演示。

That means you'll have to run the statement based on every DELETE. That should start to highlight why this is a bad idea...

The database doesn't care about sequential values - that's for presentation only.

2. SET IDENTITY_INSERT [ ^ ]。

2. SET IDENTITY_INSERT[^].

Quote：

-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').

-- SET IDENTITY_INSERT to OFF again.
SET IDENTITY_INSERT products OFF

我使用了存储过程为此：此方法可用于创建自动ID，如果用户删除了id ，那个id可以重复使用......

I have used Stored Procedure For this : This method can be used to create a automatic id, and if id is deleted by user,then that id can be reused...

Create procedure [dbo].[Add_Articles]

@catid int,
@subid int,
@title varchar(200),
@desc varchar(max),
@tags varchar(50),
@dop date,
@userid varchar(100),
@active bit

as
begin

Declare @Id int

Select @id=ISNULL(Max(ArticleID),0) from ArticleTB

Set @Id = @Id +1


Insert into ArticleTB(ArticleID,CategoryID,SubcategoryID,Title,Description,Tags,DOP,UserID,IsActive)values(@Id,@catid,@subid,@title,@desc,@tags,@dop,@userid,@active)

select 1

end

这篇关于自动生成ID .......并删除ID可重用.....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！