Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the rows started from the next identity value which created gap in the identity value. His application required all the identities to be in sequence, so this was certainly not a small issue for him.
The solution to this issue regarding gap in identity column is very simple. Let us first take a look at his application’s situation wherein there is missing identity and then we will move on to the solution.
Developers can easily deter the above issue by avoiding gap in sequence of identity column through two additional SQL Tricks of reseeding identity.
We will now see the same example with the solution to the above gap issue. On deleting records, table was reseeded with identity, which was deleted. Download complete SQL Script here.
USE AdventureWorks
GO
/* Create a table with one identity column */
CREATE TABLE TableID (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
/* Insert 10 records with first value */
INSERT INTO TableID (Col)
VALUES ('First')
GO 10
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Delete last few records */
DELETE
FROM TableID
WHERE ID IN (8,9,10)
GO
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Get current Max Value and reseed table */
DECLARE @MaxID INT
SELECT @MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
GO
/* Insert 10 records with second value */
INSERT INTO TableID (Col)
VALUES ('Second')
GO 5
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO
I hope is solution is clear to all my readers and they will use it to avoid problems related to gap in identity column. Do send me your feedback on this article and let me know if you all need further explanation.
Reference : Pinal Dave (https://blog.sqlauthority.com)
40 Comments. Leave new
I’m using the MS book “build a program now visual basic 2008” to learn sql. Unfortunately, the one database example uses the identity numbers fropm one table as the column of another table. My simple example reseeded one table when i fixed a few typos, with no way to renumber the identiy column. So, it seems the advise here is to never refer to an identity column in another table, which I will try hard to follow. It appears that one author at MS hasn’t learned this lesson yet.
Can we use other thing rather then IDENTITY Column, and it can work if 100 Users hit the Table and Always get the Unique ID.
If there is any thing please do update me …..
[email address removed due to privacy issue]
We should suppose that the ID column in this problem is not a PrimaryKey column. Because when we try to reorder a PrimaryKey column, we are in a incorrect direction. The main property of a PrimaryKey is that it should be unchanged all the time.
Cropping identity columns’s is just plain fun:
* it forces you to recheck that all tables that depend on that column are linked trough a foreign key constraint and are properly defined in diagrams
* to finally solve the multiple cascade paths could lead to
cycle issue trough writing custom update procedure/triggers
*to rethink if certain decisions you made in the passed, based on the set of business constraints at that time, are still valid now
*to make column names over the database less ambigious
*hey my cropped identity fits in a smaller type (now that my relationships are all properly defined SSMS auto-adjusts the types in dependent tables)
*finally the traces of importing data out of various databases, tables, the work of others … are starting to fade, aahh a fresh start. i feel so clean, finally i can delete that useless foreign key column that points to a non-exisiting database
it starts to look as though i built it from scratch without the clutter of other co-developers
jada jada (getting emotional)
*BUT it remains utterly pointless none the less, pointless but fun!
NOTE: (now that my relationships are all properly defined SSMS auto-adjusts the types in dependent tables) was surrounded by “dreaming” html tags
great, thanks! solves my problem.
I’ve ran into a similar problem.
I created a DEV database, where IDs started at 1.
I created a TEST database where IDs started at 10000
and in PROD the seed was 20000.
This was for a files table, which kept track of all uploaded files from an application.
Initially that was ok, so that there was a buffer between the different servers, and it was easy to tell what’s where and why.
But a day came when someone needed to copy prod data to Test. And then when testers began testing on the Test server, all the files in the 20000 range, started to be overwritten with the test files.
I tried to go back into the DB and reseed it again to like 11000, but it didn’t work, and kept on inserting in the 20000 range.
It would be great if there was a way to say ok, there are a bunch of skipped IDs, go and fill those first, and then skip over the existing ones and keep going.
Good one
don’t delet rows. Mark them resting 0, or active 1 in a column then you dont get gaps
I have two tables
s_sl_header and s_sl_detail
bothe tables have serial = serial , in the s_sl_header i have serial,shop, date,
in s_sl_detail , serial, ticket (number)
i want to find missing ticket number and should results are like this
shop ,date,missing ticket
6222 , 01-01-2012, 4432
could you please help me to get the above results in sql2000.
Thanks in advance for your help.
hi Pinal,
Really thanks to ur explanation.
But in my case, I need to retrieve the top five rows from a table which has no gaps in its primary key, which is generated from a sequence.
can u help me to achieve this output..
thanks in advance,
Ditz
Hi Pinal,
I need to explain you my situation and wanted to know, if you can help me in that : I really don’t know should I mention this issue in this area of blog or not..Please let me know, if I am on wrong path.
We are using SQL Server 2008 R2. I am doing all my work through Managment Studio. We Managed Test DB. Due to some primary key issue we reseeded our Test DB (some tables) identity columns to +20000000.(Max Identity now became from 60000+20000000).. Ok now after reseed we take DB backup and restore it to some other DB. After restore we lost whatever we reseeded +20000000..
I know there is no entry in that identity column from those number from 60000 to 20060000..and I wanted to know how should I prevent this problem happening. I don’t want to miss that identity from one Test DB to another Test DB.
Please let me know, if you need additional information
Thanks
– Jojo
Now this is simple for a table with fewer columns. what if the table has many columns?
Thank u pinal ;-)
Can anyone please tell how to fix this? Thank you.
I got a weird result after testing the example given in the tutorial. The reseeding identity column worked well except that new records now on top, not next to the last record of the first batch like this:
12 second
11 second
10 second
9 second
8 second
1 first
2 first
3 first
4 first
5 first
6 first
7 first
I think that best solution.. try if you it…
DECLARE @INC AS INTEGER
SET @INC=1
WHILE (@INC @INC) –ID=> Identity column
SET @INC = @INC+1
END
Note: before executing that you need to remove the identity insert to the column.
hi,
what if i am not using the identity column, i just based on the successful transaction, then follow by the max ranking then update the column ‘ranking’in the table.
eg : t1 ranking 7
t2 ranking 8
t3 ranking 10
the ranking value is i take the max ‘ranking- (8 + 1) then set the t3 ranking to 9, but somehow it jump to 10.
how to avoid this?
thanks.
Hola, como se podría evitar esto? porque cada vez que reinicio el correlativo se saltea?
[English translation by Google: Hello, as you might avoid this? because every time you restart the correlative is skipped?]
Hi This is really helpful.Thank you so much for your post