SQLServerCentral.com / Article Discussions / Article Discussions by Author / Discuss content posted by Larry Sumuri / Serial ID Auto Generation / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 16:34:36 GMT20RE: Serial ID Auto Generationhttp://www.sqlservercentral.com/Forums/Topic860943-2604-1.aspxThanks 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.Tue, 23 Feb 2010 15:42:19 GMTLarry Sumuri-373443RE: Serial ID Auto Generationhttp://www.sqlservercentral.com/Forums/Topic860943-2604-1.aspxI like Nigel's suggestion, comments. Check out the stuff by Jeff.Tue, 23 Feb 2010 08:54:54 GMTSQLRNNRRE: Serial ID Auto Generationhttp://www.sqlservercentral.com/Forums/Topic860943-2604-1.aspxThe article I was thinking of is [url=http://www.sqlservercentral.com/Forums/Topic817978-145-1.aspx][u][b]here[/b][/u][/url]. The key piece of information is in Jeff Modens reply, where he uses an UPDATE with (UPDLOCK).See [url=http://www.google.co.uk/search?q=UPDATE+WITH+UPDLOCK][u][b]here[/b][/u][/url] for more info.Tue, 23 Feb 2010 03:46:29 GMTnigel.RE: Serial ID Auto Generationhttp://www.sqlservercentral.com/Forums/Topic860943-2604-1.aspxA 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 [quote]SET @CurrentSID = (SELECT [SID] FROM [RefVal_SerialID])[/quote]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:[quote]SELECT RIGHT('0000000000' + cast(someInt as varchar(50)),10) [/quote] Tue, 23 Feb 2010 03:09:04 GMTnigel.Serial ID Auto Generationhttp://www.sqlservercentral.com/Forums/Topic860943-2604-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Serial+ID/69528/">Serial ID Auto Generation</A>[/B]Fri, 05 Feb 2010 17:32:49 GMTLarry Sumuri-373443