oops, IDENTITY leapfrogged a few values!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

oops, IDENTITY leapfrogged a few values!

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-10-19 : 14:43:15

I've been using IDENTITY seed 1,1 to provide the next available ID for a new record added to a table.

I set this up before I realized that records would be deleted from this table.

Today, when a new record was added, it leapfrogged five ID places from 1505 to 1510.

Can someone suggest a reliable way to automatically assign the next available value to a new record in this table, i.e. 1505, 1506, 1507 etc. Data type is INT. Records are inserted via ASP form to sproc, sql server 7.0, IIS 5.0, win 2k server.

Thank You.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-19 : 15:11:00

Why does it matter that there are gaps? Gaps occur due to deletes. This is normal.

Tara

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 15:18:35

"Gaps occur due to deletes"

(Which includes Failed Inserts)

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-19 : 15:22:37

Don't even have to have deletions: ID values created in transactions that are rolled back won't get reused either. For example:


CREATE TABLE #T (pk int IDENTITY(1,1) PRIMARY KEY, v int NOT NULL)
GO

BEGIN TRANSACTION
INSERT INTO #T (v)
SELECT n FROM Numbers WHERE n BETWEEN 1 AND 1000
ROLLBACK TRANSACTION

INSERT INTO #T (v)
SELECT 1

SELECT * FROM #T

DROP TABLE #T

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-10-19 : 15:59:11

Kristen is onto my problem. The person using the web form tried several times to submit it with dirty data. My javascript validations did not check for the bad data in this case, but SQL server did, so the INSERT was rejected.

I'm guessing that the next available IDENTITY increment was used anyway, even though the INSERT failed. Re: gaps being a problem: I need each new record to be the next available number.

One option is to check this specific field before attempting the INSERT.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-19 : 16:49:17

Reason number 5 of the Devils spawn...

Why are you forcing an artifical order of the data?

Why does it matter?

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-19 : 19:17:42

an identity isn't guaranteed to be sequential or unique.
You can get a sequential value using a trigger or by a select max or using another table to hold the next value but at the expense of perofrmance.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-20 : 00:58:41

What do you need the sequential number for? There may be a diferent solution to your problem

Kristen

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-10-20 : 12:24:39

thx all, here's the explanation...

The sequential number is required by the end users, who prefer to have the next available number assigned to a brand new record. They add a new "case" to the db chronologically, as the info comes in. They want each case to be identified by the number.

These numbers mean something to them. They search the data using ranges of these numbers, for example.

The main pitfall of IDENTITY in my case is the failed INSERT. This is when the gaps appear and this is dangerous. I'm open to another solution. Could you all provide some tips on auto-populating the PK for an INSERT using a trigger?

re: SELECT MAX, I guess

SELECT MAX (CaseID) + 1
FROM (maintable)

would give me my next available value

thx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 12:43:46

well you can enforce it with triggers...

for chronology use datetime column with default GetDate(). that should give you correct order.

you can always add sequetial numbers like:
declare @cnt int
set @cnt = 1
UPDATE MyTable SET @cnt = ColId = @cnt + 1

Go with the flow & have fun! Else fight the flow

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-10-20 : 14:22:52

thx for the tips. I decided to go with SELECT MAX + 1. Works nicely with the ASP form.

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 01:18:37

You could do it with a TRIGGER - whenever a NEW record is inserted you put (SELECT MAX(MyIDColumn)+1 FROM MyTable) in the MyIDColumn. Whenever a record is UPDATEd you, obviously, leave the MyIDColumn alone. (If you are doing the SELECT MAX+1 from ASP [I'm not sure from your message] then a) two people could get the same value at the same time and b) you have to do two round trips to the server - get the next ID, then save the record)

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-21 : 04:27:31

SELECT MAX (CaseID) + 1
FROM (maintable)

Will only work in a single user system unless you lock the table. In multi-user it will allow duplicates.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 05:43:32

In a trigger its atomic isn't it? e.g.:


DECLARE @intOffSet int
SELECT  @intOffSet = 1
UPDATE U
SET MyId = (SELECT MAX(MyID)+@intOffSet FROM MyTable),
    @intOffSet = @intOffSet + 1
FROM  MyTable U
      JOIN inserted I
          ON I.MyPK = U.MyPK

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-21 : 09:58:54

That should work - not because a trigger is atomic but because it will lock the table.
It would be better to hold the last ID in another table and update from that.
You should also allow for bulk inserts in the trigger.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 10:26:45

"You should also allow for bulk inserts in the trigger"

You thinking of something other than what I've allowed for, or just the usual FAQ of triggers being designed for single-record-sets? ;-)

I agree with a separate table for the next-ID-number. That way the trigger [and anything else that needed a block of numbers] could do

SELECT COUNT(*) FROM inserted

and increment the next_ID-number by that amount ... rather than iterate and increment-by-one ...

Mind you ... possible problem when this batch rolls-back if another user has, in the mean time, asked for a number - given that steelkilt wants no gaps - would need to include the ID table update in the transaction block until the insert is successful, or fails ...

Kristen

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-10-21 : 13:21:32

I'm doing SELECT Max + 1 in the SPROC and it is multi user (3 people have permissions to run this SPROC).

also CaseID is the PK, so if two people submit at almost the same time, one submission would have to be successful, while the other would fail b/c MAX + 1 would attempt to insert the same CaseID that just went in, no?

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 13:27:58

If CaseID is a PK or on a Unique index yes - but perhaps better that it doesn't fail at all ... so you could do something like

BEGIN TRANSACTION

SELECT @MyNextID = MAX(MyID)+1 FROM MyTable with appropriate lock

INSERT INTO MyTable VALUES (@MyNextID, ...)

COMMIT

The "with appropriate lock" is probably WITH (HOLDLOCK)

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-21 : 17:16:42

quote:
Originally posted by Kristen

"You should also allow for bulk inserts in the trigger"

You thinking of something other than what I've allowed for, or just the usual FAQ of triggers being designed for single-record-sets? ;-)

I agree with a separate table for the next-ID-number. That way the trigger [and anything else that needed a block of numbers] could do

SELECT COUNT(*) FROM inserted

and increment the next_ID-number by that amount ... rather than iterate and increment-by-one ...

Mind you ... possible problem when this batch rolls-back if another user has, in the mean time, asked for a number - given that steelkilt wants no gaps - would need to include the ID table update in the transaction block until the insert is successful, or fails ...

Kristen

The update of the id table in the trigger will lock it so no other spid can get a value so the rollback won't cause a problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources