Serial ID Auto Generation

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

  • 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)

  • 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.

  • 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

  • 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.

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

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