Generating a unique number - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Generating a unique number

Author

Topic

jgroom
Starting Member

7 Posts

Posted - 2005-06-24 : 03:45:53

I have tried to create a unique number generator (in a Stored Procedure) with code similar to the following :-

SELECT last_number FROM generator
UPDATE generator SET last_number=last_number+1

Whilst this generates the next sequential number there is nothing to prevent a secound user getting the same number before the first user updates the counter.

What I need is something to prevent this happening so that I can guarrantee that each user gets a unique (sequential) number.

NB - I do not want to use the INDENITY option.

Regards JEG

John Groom

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-24 : 05:23:33

You can make the statemant atomic, like so:

DECLARE @n INT
UPDATE generator SET
	last_number = last_number + 1
	,@n = last_number
SELECT @n

rockmoose

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-24 : 06:50:27

"NB - I do not want to use the INDENITY option."

So you COULD use the tools that SQL provides for you, but you dont WANT to.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

jgroom
Starting Member

7 Posts

Posted - 2005-06-24 : 08:29:23

Rockmoose

That's a good idea - I will try that.

DonAtWork

If IDENTITY would have satisfied my other needs - then I would have used it. Go back to sleep !!

John Groom

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-06-24 : 19:24:41

Couldn't you wrap your original approach in an explicit transaction?

BEGIN TRAN
SELECT last_number FROM generator
UPDATE generator SET last_number=last_number+1
COMMIT TRAN

HTH

=================================================================
'Tis with our judgements as our watches: none Go just alike, yet each believes his own. -Alexander Pope, poet (1688-1744)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-24 : 19:36:31

I did this the other way around with the idea that after the row is updated, it will be unavailable to other users running the same procedure until the transaction is committed, and the select will take place before anyone else can do an update.



BEGIN TRAN
UPDATE generator SET last_number=last_number+1
SELECT last_number FROM generator
COMMIT TRAN

CODO ERGO SUM

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-06-27 : 15:55:56

I should have mentioned this in my earlier response but the WITH HOLDLOCK index hint would be appropriate here.

HTH

=================================================================
'Tis with our judgements as our watches: none Go just alike, yet each believes his own. -Alexander Pope, poet (1688-1744)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources