如何在MSSQL中重置自动增量?

如何在MSSQL中重置自动增量?

问题描述:

我一直在使用我的数据库这个问题,即使它已被删除,它仍然会增加id列。让我说我有一个从1到16的id列。现在我想添加一个项目,它的ID将是17.但当我删除项目17并决定再次添加项目时,它将进入18.所以现在我的id列将类似于1-16和18。它在我的id列中创建了这个巨大的差距,因为它继续自动递增id列。我想要发生的是,当我删除一个项目时,我希望它从最后一个16开始。所以下一个id应该是17.我希望这对你们有意义。



我尝试过:



这是sqp脚本的一部分:



I've been having this problem with my database where it kept on incrementing the id column even though it has been removed. Lets say i have an id column from 1 - 16. Now i want to add an item, its id will be 17. But when i remove item 17 and decided to add an item again, it will proceed to 18. So now my id column would be like from 1-16&18. Its creating this huge gap in my id column because of its continued auto-incrementing the id column. What i want to happen is, when i remove an item, i want it to start from the last id which is 16. So the next id should be 17. I hope this makes sense for you guys.

What I have tried:

Here is the part of the sqp script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[guitarItems](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[type] [varchar](50) NOT NULL,
	[brand] [varchar](50) NOT NULL,
	[model] [varchar](50) NOT NULL,
	[price] [float] NOT NULL,
	[itemimage1] [varchar](255) NULL,
	[itemimage2] [varchar](255) NULL,
	[description] [text] NOT NULL,
	[necktype] [varchar](100) NOT NULL,
	[body] [varchar](100) NOT NULL,
	[fretboard] [varchar](100) NOT NULL,
	[fret] [varchar](50) NOT NULL,
	[bridge] [varchar](100) NOT NULL,
	[neckpickup] [varchar](100) NOT NULL,
	[bridgepickup] [varchar](100) NOT NULL,
	[hardwarecolor] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[guitarItems] ON

你可以做到,但要小心。

标识栏不保证唯一性。 />


我们假设您有15个重复rds的ID为1-15,然后你删除行5-10

你可以用

You can do it, but be careful.
An identity column doesn't guarantee uniqueness.

Let's assume you have 15 records with ids of 1-15, then you delete rows 5-10
You can reset the seed with
DBCC CHECKIDENT('<tablename>', RESEED, <startvalue>, <increment>)



所以你重新调整为5,增量为1


so you reseed to 5 with an increment of 1

DBCC CHECKIDENT('guitarItems', RESEED, 5, 1)



然后你开始创建新记录,从5开始。

当你到达10时,你的插入将失败。它不会跳到16。



一般来说,重新播种以避免ID中的间隙既不必要又危险。


You then start creating new records, which will start at 5.
When you get to 10, your insert will fail. It won't skip to 16.

In general, reseeding to avoid gaps in ids is both unnecessary and dangerous.