Serial ID Auto Generation

Question

Serial ID Auto Generation

LSumuri

SSC Enthusiast

Points: 145
More actions
February 5, 2010 at 5:32 pm

#215387

Comments posted to this topic are about the item Serial ID Auto Generation

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

nigel. SSChampion Points: 11636 More actions · Answer 1

A couple of issues here:

1. Concurrency - wrapping your code in a transaction is not going to ensure that it cannot be executed concurrently by two or more processes, resulting in potential duplicates being generated. The line

????????????????

SET @CurrentSID = (SELECT [SID] FROM [RefVal_SerialID])

could very easily be executed by two processes and get the same result.

There was a post on this very topic somewhere here recently when I find it I'll post the link.

2. Left padding with zeroes - don't need all those IF's, just do it like this:

SELECT RIGHT('0000000000' + cast(someInt as varchar(50)),10)

Nigel
How to post questions

nigel. SSChampion Points: 11636 More actions · Answer 2

The article I was thinking of is here. The key piece of information is in Jeff Modens reply, where he uses an UPDATE with (UPDLOCK).

See here for more info.

Nigel
How to post questions

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I like Nigel's suggestion, comments. Check out the stuff by Jeff.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LSumuri SSC Enthusiast Points: 145 More actions · Answer 4

Thanks Folks, for all the good suggestions and corrections, I will check out the link and rewrite the script to cater for the scenarios mentioned and post again here for more reviews.