Click here to Skip to main content
15,888,984 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
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
Posted
Updated 8-May-17 19:24pm

1 solution

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
SQL
DBCC CHECKIDENT('<tablename>', RESEED, <startvalue>, <increment>)

so you reseed to 5 with an increment of 1
SQL
DBCC CHECKIDENT('guitarItems', RESEED, 5, 1)

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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900