Serial ID Auto Generation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Larry Sumuri » Serial ID Auto Generation

Serial ID Auto Generation

Rate Topic

Display Mode

Topic Options

Author

Message

Larry Sumuri-373443

Posted Friday, February 05, 2010 5:32 PM

Grasshopper

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

nigel.

Posted Tuesday, February 23, 2010 3:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:31 AM
Points: 1,164, Visits: 2,601

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

nigel.

Posted Tuesday, February 23, 2010 3:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:31 AM
Points: 1,164, Visits: 2,601

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

SQLRNNR

Posted Tuesday, February 23, 2010 8:54 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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

Larry Sumuri-373443

Posted Tuesday, February 23, 2010 3:42 PM

Grasshopper

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 »

Permissions