Identity specification column skipping values

Question

0.00/5 (No votes)

See more:

I have a table of Items, and the ID for each is an Identity Specification Column.

I wrote a stored procedure to update the table to X item numbers which are then filled in based upon the ItemID.

The problem that I'm finding is that for some some reason I am getting missing/skipped values in the identity column.

This is the Stored Procedure to update the table to the desired ItemNum and then based upon each ID the Items are filled in through another source using the ItemID.

What I have tried:

SQL

<pre>USE [Inventory]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spUpdateItemTableTo]
(
	@intUpdateTo INTEGER -- Value to update to
)
AS
BEGIN

	DECLARE @intCounter AS INTEGER;

	SET @intCounter = ISNULL((SELECT TOP 1 [tblItems_ItemID] FROM
		[tblItems] ORDER BY [tblItems_ItemNum] DESC),1);

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Simple loop to insert default values (default Identity Values)
	WHILE @intCounter < @intUpdateTo
		BEGIN
			INSERT INTO [tblItems] DEFAULT VALUES;
			SET @intCounter = @intCounter + 1;
		END;

END;

Posted 16-Oct-22 16:07pm

RSquared64

Updated 17-Oct-22 0:22am

Add a Solution

2 solutions

Solution 2

In addition to the issues mentioned in solution 1, it is also possible for identity columns to "jump" when the server restarts, due to the way they are implemented:

SQL Server 2012 Auto Identity Column Value Jump Issue[^]

To avoid that, you can either add trace flag 272, or generate the identity values explicitly using an un-cached sequence:

CREATE SEQUENCE (Transact-SQL) - SQL Server | Microsoft Learn[^]

If you're using SQL Server 2017 or later, there is also the new IDENTITY_CACHE option:
SQL SERVER - Identity Jumping 1000 - IDENTITY_CACHE - SQL Authority with Pinal Dave[^]

Posted 17-Oct-22 0:22am

Richard Deeming

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2022-10-16T19:08:00

Solution 1

Identity values are not guaranteed to be contiguous: if you delete rows, then the identity value assigned to that row is not "recycled" later, so you get "gaps" in the values. That isn't a fault, it's by design so that if other tables (or other rows) reference the deleted row's identity value they aren't "attached" to a random new row when it is inserted.

Identity values are supposed to be unique, nothing else. If you want contiguous numbers, then use ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[^] - but remember that the values will not necessarily be the same if you run the code twice as other users could change your data between runs.

You should never try to manually force Identity values to be contiguous: it isn't what they are there for and can corrupt your entire DB if you aren't very, very careful!

Posted 16-Oct-22 19:08pm

OriginalGriff

Comments

RSquared64 17-Oct-22 8:42am

I am using MS SQL Server 2017.

No records are ever deleted from this table, and the ItemID is always incremented by one. Also, during the ItemID updates, no users are making any changes that would effect it. They have no access to the new records until the ItemID is added AND the Item information is updated to each matching ItemID row.

It's sounding like the best option for me would be to modify the stored procedure to get the last ItemID, update to the desired value value as it is doing right now and completely skip using an Identity column at all?

RowNumber, from what I've read isn't an exact way from what I've seen.

I'm not familiar with the CREATE SEQUENCE, so I will research that as well.

I'll test and mark answered as I find a solution.

Looking at everything, Setting the Identity Cache Off Seems like the best answer. Running the update now.

OriginalGriff 17-Oct-22 9:34am

No. That's a recipe for intermittent errors that compromise your data integrity: that aren't normally noticed for weeks or months after the problem actually happened, and which are a nightmare to sort out the data.

Remember, SQL Server is a multiuser environment - so more than one person can be executing your SQL at the same time, and thus get the same results. All the related data then gets store against the wrong ID and storing it out becomes a paper chase. And adding Transactions makes the problem worse, not better because it extends the timer interval for simultaneous generation of a new ID.

Do not treat Identity values as contiguous: they do not have to be and you will come unstuck if you assume they are. Why do you think you need a contiguous sequence?

RSquared64 17-Oct-22 9:45am

Ok, Griff, but the problem for me (I'm coming in for a client who is setting the requirements), the ItemID's HAVE to be contiguous. I was working around this by doing the updates to ItemID and unless the flag that the records was completely updated was set to true, then no user could access that record. Once the ItemID was present AND all the record info was there, could they update it. Since no records will ever be deleted, that's the one issue I didn't have to worry about. So the problem is then still how to enter the completely contiguous ItemID's, which it sounds will have to be a more manual computation - Start - Stop insert like I'm using in the Stored Procedure and throw out the Identity Specification completely. I will be doing a big insert for all existing ItemIDs then it will be only a few at a time after the initial setup, figure 1 to 20 something, so it will be a quick and clean update after the initial insert of ItemIDs to the table.

OriginalGriff 17-Oct-22 10:27am

Then don't use an Identity as the column he "sees".
Instead, use a column you can UPDATE once the row is added, using the ROW_NUMBER function to give you a contiguous record. That way, you get the contiguous numbering your client demands, without any of the risks. You would still use an Identity column to provide your ordering because SQL will handle that but the actual numbers become client focussed instead of internally generated. Faffing with Identity is just asking for trouble - as Richard has rightly added!

RSquared64 17-Oct-22 14:32pm

Ok, sounds pretty good. Was just using the Identity since the ItemID's were unique incremental values and worked perfectly for any relationships. I think I'll use a "combo" of both ideas. Thanks for your help.

OriginalGriff 17-Oct-22 15:11pm

You're welcome!