Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Serial ID Auto Generation Expand / Collapse
Author
Message
Posted Friday, February 5, 2010 5:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:19 PM
Points: 17, Visits: 86
Comments posted to this topic are about the item Serial ID Auto Generation
Post #860943
Posted Tuesday, February 23, 2010 3:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:39 AM
Points: 1,181, Visits: 2,651
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
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #871006
Posted Tuesday, February 23, 2010 3:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:39 AM
Points: 1,181, Visits: 2,651
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
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #871015
Posted Tuesday, February 23, 2010 8:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:13 PM
Points: 21,755, Visits: 15,458
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #871198
Posted Tuesday, February 23, 2010 3:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:19 PM
Points: 17, Visits: 86
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.
Post #871521
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse