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
Hi Pinal,
I’m wondering if we have records like 1,2,3,5,6,8,9… then what? So here is the revised version of the above SQL statements which will insert into middle and at bottom.
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 records from middle and bottom both*/
DELETE
FROM TableID
WHERE ID IN (4,7,11,12)
Go
/* Check the records in table */
SELECT *
FROM TableID
GO
/*
Now check the missing identity values
To Do so we will create a memory table with a sequential number,
from 1 to the ident_current value and join that to the live table.
*/
Declare @rows Int
Declare @numberOfRowsToInsert Int
declare @seq table (
seq int not null primary key
)
/* Get current Max Value and reseed table */
SELECT @rows = MAX(ID) FROM TableID
DBCC CHECKIDENT(‘TableID’, RESEED, @rows)
set @rows = (select Ident_Current(‘TableID’)) + 5
/* Insert values into Memory table */
declare @i int
set @i = 1
while @i <= @rows
begin
insert @seq values( @i )
set @i = @i + 1
end
/* Now set the Identity Insert On, so that we can insert any number */
Set Identity_Insert TableID On
INSERT INTO TableID(Id,col) Select seq,’Third’
From @seq left outer join TableID T on seq = T.ID
Where T.ID Is Null
Set Identity_Insert TableID OFF
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO
-Vinit
Why do people put so much meaning in the order of number, if it is an identity, it should just be unqiue, doesn’t matter in the order.
if the order matters, generate it at run time!
If he is relying on the uninterrupted order of the keys, then that is a design flaw.
How About if your customer doesn’t wan gaps in the client ids ?
How about if you develop a software for a customer in a country where the law requires that there must be no gaps between order numbers ?
ID column should have no business meaning so it doesn’t matter if it skips 1, 2 or a 1000 values. It just needs to uniquely identify a row (or entity).
I agree with Anthony. As long as the values are unique, it should not matter what they are. If a solution relies on an uninterrupted sequence of identity values, then the solution is poorly designed.
IMHO, any solution that uses integers as identity values should also be able to use GUIDs without any loss of basic functionality. If you can’t do that, you need to rethink what you’re doing.
Kind of funny how we make assumptions on the design and not fully understanding why the junior developer did what they did.
Without actually knowing what they were doing, kind of hard to say it was a design flaw, I think the whole point of the article was how to reindex identity column so that they are in sequence.
Now if had been something I worked, then I would not care what is in the id column, I would have a second column that would be seen by the user.
i.e. patient number, product sku, the id column would be something that could use to build a relationship lookup or nothing at all. Again depending on the design.
Nice article, but I am sure that the author pointed out how to get around it and then probably asked the developer why they were doing that and point him/her in the right direction. :)
People that need to do this are not understanding the right way to do this. You don’t need consecutive IDs, you only need UNIQUE IDs.
IF you need consecutive IDs, create a VIEW that simply ORDERS the table with consecutive IDs.
Trust me, you don’t need consecutive IDs in the TABLE.
I fully agree with IrishManInUSA the basic purpose of id column is defeated if we rearrange the sequence of id. The Id is unique identity given to a row its not a serial no.
(though in the article this is meant to be )
hi Vinit,
use the updated trigger on the table and get the result in sequence.
like 1,2,3,4,5,6,7
no 1,2,3,5,7,10
IrishManInUSA is 100% right
Dear Syyed,
Can you kindly elaborate your solution about triggers
Hi All,
In the above example, the identity missing was found by manually. Is there any way to find the same using SQL statement ?
ie, How we can find the list of tables whose identity was missed(not is sequential order) within the entire database?
We are having an issue with the identify column of one of our normalized tables. The primary key (identity) of TableA is referenced by TableB and TableC.
When we went to move this table from one database to another, the identity column was reseeded. This of course caused havoc with our data since records were being paired incorrectly.
Is there a way around this?
Some of the critics here are forgetting about inherited code and data (not OO inheritence, hand-me-down code).
I just inherited a DB table that contains an ID column and an order number column that was not originally an Identity column. So, all records are unique on the ID PK column, but *should* also be unique on the order number as well…they are NOT.
Some records have a NULL order number and some order numbers are duplicated. The difficulty is in making the decisions of how to rectify these issues:
Can we delete the data if it is older? That is simple.
If not, how to make this an Identity column and still retain the existing order numbers for historical purposes? SQL Server 2005 doesn’t let you make an existing column IDENTITY: “You cannot modify an existing table column to add the IDENTITY property.”
How to find gaps (un-used numbers) around the same date range of the records with NULL or duplicate order numbers to try to use those to fix the bad records…we could simply use the next highest order number, but the users intuitively know the approximate date of the orders based on the order number and this would throw them off.
If anyone has suggestions for these real-world issues, I’d be interested in hearing them.
Hi pinal,
thank you
thank you very much
Thanks & regrads
Harishkumar.M
My humble experience of past few years (as a Sr.System Analyst & DB Architect) support the comments by Mr.IrishManInUSA also I support Mr.Ed McInerney.
I believe Mr. Pinal was just conveying about such a method.
The application of this thing may vary depends on situation. Anyway it’s very useful for a table with a dedicated column for customizable sort order, rather than the concept of primary key resorting.
It was very helpful to me.
Thanks
One reason I can think of when this may be needed.
Lets say we have a lookup table where identity field was tinyint rather than int. Lets say someone entered 250 values that are now not required.
Before now we delete those values and want to insert 10 new lookup values. But I will not be able to do so as I would have run out of numbers. Resetting the identity seed will allow me to enter my values.
This is just one example of where resetting identity seed may be helpful….. There may be other ones…
hi sir,
i am not a regular reader but i have used ur tips and tricks for my knowledge.I am some confused about identity , that when i have 10 records in that i have deleted only one record that is 7 th record. then my identity no 7 will be missing for ever and even my identities are not in order .so will u let me know me how to reorder them .
Thank You,
Hello Jeswin,
Deleted identity value is not used. There are some workarounds to get identity of deleted records. One is described here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017
but that is not recommended for large tables as it degrade the performance of insert statements.
Regards,
Pinal Dave