How to reseed the identity column in SQL Server?

Question

I have a table in SQL Server called Personal_Info. Tthis table has a column called Vol_ID and the (Is Identity) property is Yes.

Now I inserted about 175568 rows as a test. And then I deleted about 568 rows, BUT when I insert the data again the column Vol_ID start from 175569 not from 175001.

I want to insert the data and let the column Vol_ID start from the last number in it. For example if the last row is 15, the next row should be start from 16, even if I already inserted but I delete it.

I need a stored procedure or a trigger that can force the insert operation to filled that missing id's, check the last exist row and if there is missing id's after the exist one then filled it.

I have this procedure to make the insertion



      USE [VolunteersAffairs] 

    go 

    /****** Object:  StoredProcedure [dbo].[AddVolunteer]    Script Date: 05/02/2014      21:12:36 ******/ 
    SET ansi_nulls ON 

    go 

    SET quoted_identifier ON 

    go 

    ALTER PROCEDURE [dbo].[Addvolunteer] @Vol_Name                 NVARCHAR(255), 
                                         @Vol_Zone                 NVARCHAR(255), 
                                         @vol_street               NVARCHAR(255), 
                                         @Vol_Sex                  INT, 
                                         @Vol_Date_of_Birth        DATE, 
                                         @Vol_Home_Phone           INT, 
                                         @Vol_Work_Phone           INT, 
                                         @Vol_Mobile1              INT, 
                                         @Vol_Mobile2              INT, 
                                         @Vol_Email                NVARCHAR(255), 
                                         @Vol_Job                  NVARCHAR(255), 
                                         @Vol_Affiliation          NVARCHAR(255), 
                                         @vol_Education            INT, 
                                         @vol_Education_Place      NVARCHAR(255), 
                                         @vol_Education_Department NVARCHAR(255), 
                                         @vol_Interesting          INT, 
                                         @vol_Notes                NVARCHAR(255), 
                                         @Team_ID                  INT 
    AS 
        INSERT INTO personal_info 
                    (vol_name, 
                     vol_zone, 
                     vol_street, 
                     vol_sex, 
                     vol_date_of_birth, 
                     vol_home_phone, 
                     vol_work_phone, 
                     vol_mobile1, 
                     vol_mobile2, 
                     vol_email, 
                     vol_job, 
                     vol_affiliation, 
                     vol_education, 
                     vol_education_place, 
                     vol_education_department, 
                     vol_interesting, 
                     team_id, 
                     vol_notes) 
        VALUES      (@Vol_Name, 
                     @Vol_Zone, 
                     @vol_street, 
                     @Vol_Sex, 
                     @Vol_Date_of_Birth, 
                     @Vol_Home_Phone, 
                     @Vol_Work_Phone, 
                     @Vol_Mobile1, 
                     @Vol_Mobile2, 
                     @Vol_Email, 
                     @Vol_Job, 
                     @Vol_Affiliation, 
                     @vol_Education, 
                     @vol_Education_Place, 
                     @vol_Education_Department, 
                     @vol_Interesting, 
                     @Team_ID, 
                     @vol_Notes)

Please Help me to write the missing id's

One word of advice: ***DON'T DO THIS!*** You should ***NEVER EVER*** "recycle" identity values that have been used previously for new rows. Don't do it - you'll end up in database hell if you do! — marc_s, May 02 '14 at 18:16
[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 02 '14 at 18:16

score 3 · Accepted Answer · edited May 23 '17 at 11:57

you need to reseed the identity column like below

DBCC CHECKIDENT(Personal_Info, RESEED, 175001)

Quoted from MSDN

Permission:

Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

So. it's either the owner of the table (OR) DBA can do the reseed of identity column and not normal user. moreover, why will you even allow your application user to reseed identity columns value? that won't make sense at all.

EDIT:

In that case check @Brennan answer below. in a nutshell, you should perform your insert from stored procedure. I mean your APP should call SP and perform the insert. Like below(a sample code). Also, in such case remove the identity property of vol_id column (vol_id should just be vol_id int not null unique)

create procedure sp_insert-person
@name varchar(10)
as 
begin
declare @last_id int;
select @last_id = top 1 Vol_ID from Personal_Info order by Vol_ID desc;
insert into Personal_Info(Vol_ID,name) 
values(@last_id+1,@name);
end

Your application will call the procedure like

exec sp_insert-person @name = 'user2251369'

Final EDIT:

See this post RESEED identity columns on the database about why you should avoid reseeding IDENTITY value (A nice small explanation given by marc_s).

Your Final procedure should look like below

USE [VolunteersAffairs]
GO
/****** Object:  StoredProcedure [dbo].[AddVolunteer]    
 Script Date: 05/02/2014      21:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[AddVolunteer]
@Vol_Name nvarchar(255), 
@Vol_Zone nvarchar(255), 
@vol_street nvarchar(255), 
@Vol_Sex int, 
@Vol_Date_of_Birth date, 
@Vol_Home_Phone int, 
@Vol_Work_Phone int, 
@Vol_Mobile1 int,    
@Vol_Mobile2 int, 
@Vol_Email nvarchar(255), 
@Vol_Job nvarchar(255), 
@Vol_Affiliation nvarchar(255), 
@vol_Education int, 
@vol_Education_Place nvarchar(255), 
@vol_Education_Department nvarchar(255), 
@vol_Interesting int, 
@vol_Notes nvarchar(255), 
@Team_ID int
As
BEGIN
DECLARE @last_vol_id int;
select top 1 @last_vol_id = Vol_ID from Personal_Info order by Vol_ID desc;

insert into Personal_Info
(Vol_Id,
Vol_Name, 
Vol_Zone, 
vol_street, 
Vol_Sex, 
Vol_Date_of_Birth, 
Vol_Home_Phone, 
Vol_Work_Phone, 
Vol_Mobile1, 
Vol_Mobile2, 
Vol_Email, 
Vol_Job, 
Vol_Affiliation, 
vol_Education, 
vol_Education_Place, 
vol_Education_Department, 
vol_Interesting, 
Team_ID, 
vol_Notes) 
values 
(@last_vol_id+1,
@Vol_Name, 
@Vol_Zone, 
@vol_street, 
@Vol_Sex, 
@Vol_Date_of_Birth, 
@Vol_Home_Phone, 
@Vol_Work_Phone, 
@Vol_Mobile1, 
@Vol_Mobile2, 
@Vol_Email, 
@Vol_Job, 
@Vol_Affiliation, 
@vol_Education, 
@vol_Education_Place, 
@vol_Education_Department, 
@vol_Interesting, 
@Team_ID, 
@vol_Notes);
END

this is will work for me not for the normal user who should use the application, if he/ she insert some rows and then delete it then when they insert a new data it should be check the last row and then reseed it. thanks — , May 02 '14 at 17:53
@user2251369, see edited answer. You shouldn't permit your app user to do this task at all. — Rahul, May 02 '14 at 17:57
who ask me to built this application told me that the serial numbers are most important and it should be automatically insert. — , May 02 '14 at 17:59
@user2251369, in that case check Brennan answer below. in a nutshell, you should perform your insert from stored procedure. I mean your APP should call SP and perform the insert. — Rahul, May 02 '14 at 18:01
@user2251369, see edited answer with an example to do the same with procedure — Rahul, May 02 '14 at 18:07
Rahul Will you please alter my procedure and marge you proc with mine. — , May 02 '14 at 18:29
@user2251369, it's straight forward and you should be able to get it. Anyways, see edited answer. — Rahul, May 02 '14 at 18:39
I won't forget, but you should know the identity of Vol_ID is yes how could i insert it or you mean should i put the identity NO. — , May 02 '14 at 18:44
and look at this error :Msg 156, Level 15, State 1, Procedure AddVolunteer, Line 23 Incorrect syntax near the keyword 'top' — , May 02 '14 at 18:45
@user2251369, it's fixed, check edited answer (`top 1` was in wrong place). Also, for second question; that's what I said that you will have to make `vol_id` a `NON-IDENTITY` column; cause responsibility of inserting next incremented value is now taken care by the SP.Make your `vol_id` column as `vol_id int not null unique`. Go through the entire post; I have mentioned all the points there. — Rahul, May 02 '14 at 18:57

score 2 · Answer 2 · answered May 02 '14 at 17:56

What you are trying to do is not what the identity column was made for - there is no guarantee that there won't be gaps, and deleting records does not re-use the missing identities.

If you really need a sequential, auto increment id field, with no gaps, then you are better of either using a stored procedure to do your inserts (to which you will need to add the logic to re-use missing id's), or else define a trigger that enforces what you want - but trying to force the identity column to do what you are trying to do is the wrong way to go about it.

Thanks man but if could write an example of a stored procedure or a trigger that will force the application to write the missing id's that will be helpful, thanks again — , May 02 '14 at 18:02

score 1 · Answer 3 · answered May 05 '14 at 15:54

1

This is the magic. Do this just before your insert.

declare @i int
select @i=max(id) from Personal_Info
DBCC CHECKIDENT (Personal_Info, RESEED, @i)

answered May 05 '14 at 15:54

RosSQL

325
1
5

score 0 · Answer 4 · edited May 23 '17 at 12:05

0

So as other have said, you can reseed the identity. However, this will only work if you remove at the end. Filling in the gaps in the identity will be really challenging and you will end up having to turn off the identity.

If this is a small case for seeding data, you could turn identity insert on. enter link description here

SET IDENTITY_INSERT {your table} ON

Here is another question highlighting the other concerns

edited May 23 '17 at 12:05

Community

1
1

answered May 02 '14 at 17:57

TheNorthWes

2,661
19
35

1

This is never a good idea. If you are doing this .. then it's better not to have identity column at all. – Rahul May 02 '14 at 18:03
Set Identity_Insert On is never a good idea? Any absolute is hard to defend. I use it a lot when I need to seed a table during development and wiped something out. It is there for us to use, but I do agree the cases are rare. – TheNorthWes May 02 '14 at 18:08
with `not good idea`; I mean, instead of force writing identity, reseed the identity value or some other way. `SET IDENTITY_INSERT` by default is `OFF`. Definitely there is some good reason behind that. – Rahul May 02 '14 at 18:14
Oh I see, I agree. What I was trying to say is reseeding the identity won't solve the full problem. **IF** there is the potential that the user could delete items from the middle of the list, then reseeding the identity won't fix those gaps. – TheNorthWes May 02 '14 at 18:17

How to reseed the identity column in SQL Server?

4 Answers4