Adding new sequence number as an identifier

Question

There is a table which has a varchar identifier. There are also several SP which generate this number by getting max and incrementing by 1.

How Can I Lock the table so that I could reserve ID's for e.g. 50 new records, and none user in the meantime hasn't inserted record in my "range" I have obtained.

Is it possible something like this?

because It is about 20 characters formatted value (alphanumeric value) — John
– John, Commented Jan 23, 2012 at 12:34
@John - Insert a dummy record with currentmax + 50 and perform the rest of your work after that. A better solution might be to get the next value from a seperate table where you an increment at will but that would require you to change all your stored procedures. — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Jan 23, 2012 at 12:40

JNK · Accepted Answer · 2012-01-23 13:16:53Z

2

This is an incredibly difficult problem to solve, which is why every RDBMS implementation has a built in auto-incrementing identifier data type.

USE AN IDENTITY FIELD

That is really the only valid solution to this. If you have a high transaction rate, there will be an incredible amount of locking and blocking in your code to perform something that the engine is designed to handle automatically for you.

If you need an alphanumeric field, there's no reason you can't have an identity field and a varchar and use them as a dual key.

answered Jan 23, 2012 at 13:16

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andriy M · Accepted Answer · 2012-01-24 09:48:02Z

0

Do the insert inserts in a transaction.

Example:

BEGIN TRANSACTION MyIdentifierRange
     EXEC usp_createNewIdentifier 
COMMIT TRANSACTION MyIdentifierRange

edited Jan 24, 2012 at 9:48

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Jan 23, 2012 at 15:13

Shawn H

6007 silver badges16 bronze badges

Collectives™ on Stack Overflow

Adding new sequence number as an identifier

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related