How to reset auto-increment in SQL Server?

Question

I've been having this problem with my database where it kept on incrementing the id column even though it has been removed. To better understand what I meant, here is a screenshot of my gridview:

As you can see from the id column, everything is fine from 11 - 16. but it suddenly skipped from 25 - 27. 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.

Here is also part of the SQL 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)
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO

 SET ANSI_PADDING OFF
 GO

Possible duplicate of [Identity increment is jumping in SQL Server database](http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database) — Gurwinder Singh, May 09 '17 at 04:05
@GurwinderSingh - how come this might be duplicate to that link? we don't even have the same solution to this problem. I've checked the link and its far more complex than what i'm asking. — BrunoEarth, May 09 '17 at 04:11
`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, May 09 '17 at 04:48
[Stop making assumptions about `IDENTITY`](https://sqlblog.org/2009/10/12/bad-habits-to-kick-making-assumptions-about-identity), stop worrying about gaps - they are really **NOT** a problem! — marc_s, May 09 '17 at 04:48
The **only** (really: the _only_) job of a (generated) primary key value is to be unique. Nothing else. Gaps are not an issue and if you are relying on them, you have a serious flaw in your design. **If** you require a gapless numbering for legal reasons (e.g. an invoice number) you have to use something different. — , May 09 '17 at 05:46

score 4 · Answer 1 · answered May 09 '17 at 04:22

4

You can use:

DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1);

Before using it, visit link: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql

answered May 09 '17 at 04:22

Tomato32

2,145
1
10
10

wouldn't this reset it to 1? – Norbert Forgacs May 09 '17 at 04:22
@berthos: Yeah. Set the value that you want. Just example, my friend. – Tomato32 May 09 '17 at 04:25
Yea, sure, I just wanted highlight the fact that it has to be implemented to his needs – Norbert Forgacs May 09 '17 at 04:29
After reseeding the identity column duplicate values may occur. – Zohar Peled May 09 '17 at 07:04
1

the value 0 must be set for RESEED, DBCC CHECKIDENT ("YourTableNameHere", RESEED, 0), so the ID of the first record to be created will be 1 – HamidReza Apr 20 '21 at 07:26

score 2 · Accepted Answer · answered May 09 '17 at 04:49

Primary autoincrement keys in the database are used to uniquely identify a given row and shouldn't be given any business meaning. So leave the primary key as it is and add another column like guitarItemsId. Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrease the guitarItemsId column of all rows that have the guitarItemsId greater than the one you are currently deleting.

Also, remember that you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate the referential constraints.

@BrunoEarth If the answer helped solve your problem, you might want to mark it as the accepted answer. Thanks! — samithagun, May 25 '17 at 08:53

How to reset auto-increment in SQL Server?

2 Answers2