Serial # generation

  • I have the following procedure:

    DECLARE @id int

    DECLARE @result table(id id)

    update generatepk

    set gpk_currentnumber = gpk_currentnumber + 1

    OUTPUT inserted.gpk_currentnumber INTO @result

    WHERE gpk_pk = @area

    select * FROM @result

    I am still getting duplicate #'s every so often. Any ideas how I can make sure I get a unique # back everytime?

    TIA,

    Mike

  • The Update statement you have used is just incrementing the column value by 1 which does not make them unique.

    Can you please explain your requirement in details. You can try ROW_NUMBER to get an incremental serial #

  • I am using this as a PK for other tables. I am choosing not to use an identity column.

  • UPDATE generatepk WITH (UPDLOCK)

    SET ...

    may help, especially if you are using SNAPSHOT isolation.

  • If you put a unique index on that column, or make it a Primary Key column, you won't be allowed to have duplicates in there and will no longer have the problem. You will, however, have to account for an attempted duplicate insert in your procedure, so that it will handle the situation if you end up trying to insert a duplicate number.

    This may be best handled by a lock, as previously suggested, as when a second instance of your process/procedure tries to do an insert at the same time as the first the second will have to wait a microsecond or two, while the first insert completes, then it can proceed.

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • I am trying to prevent having to handle a duplicate pk insert.

    Here is the structure of the table:

    gpk - Table Name

    gpk_currentnumber - last PK used

    There are 200 records in this table. Each user will "hit" the table many times. One user may get a "system_note" pk, while another user is getting an "order" pk. Will the UPDLOCK cause blocking or will it only lock for that field? If I only have 100-200 users, is the blocking an issue anyway?

    Thanks,

    Mike

  • OK, I have to ask, why do you choose not to use an identity column? That is the simplest solution to this situation, really.

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • Stamey (10/21/2010)


    OK, I have to ask, why do you choose not to use an identity column? That is the simplest solution to this situation, really.

    Chris

    For a PK, it is a simple solution. However, in the code base we are using, it is not so simple. Also, I want to use the same technique for other #'s like order and invoice #'s. Those aren't pk, however, they have to be unique and in sequence. I can't throw away any #'s.

    Mike

  • Mike,

    The sequence table mentioned by the previous poster works very well in most cases. I work quite a bit with a large Point of Sale application that uses this type of sequence table to generate ticket numbers, which have to be unique. I've seen up to 20 stores with about 10 cash registers in each store during the Xmas rush have no problem getting ticket numbers using this technique.

    If there is a failed ticket insert, however, there will be a gap in the ticket numbers. It's never worth it to go back and try to use the missing ticket number.

    I have no experience with anything other than getting one ticket number at a time, but I don't see any reason getting a range of unique numbers would be any different.

    Todd Fifield

  • mike 57299 (10/19/2010)


    I have the following procedure:

    DECLARE @id int

    DECLARE @result table(id id)

    update generatepk

    set gpk_currentnumber = gpk_currentnumber + 1

    OUTPUT inserted.gpk_currentnumber INTO @result

    WHERE gpk_pk = @area

    select * FROM @result

    I am still getting duplicate #'s every so often. Any ideas how I can make sure I get a unique # back everytime?

    TIA,

    Mike

    There's only 1 way to do this.

    DECLARE @NextID INT

    UPDATE gpk_pk

    SET @NextID = gpk_currentnumber = gpk_currentnumber + 1

    WHERE gpk_pk = @area

    Of course, that's only the tip of the iceberg. To do it right, you also have to do some error check and allow for more than 1 ID. I just happen to have such a thing. You'll need to change the table name and a couple of other things but I guarantee no dupes and I guarantee that if you keep the call to the following out of an explicit transaction, I also guarantee no deadlocks.

    CREATE PROCEDURE dbo.GetNextID

    /****************************************************************************************

    Purpose:

    This stored procedure is used to get a NextID for the table identified by the @KeyID

    parameter. It will "reserve" a block of ID's according to the @IncrementValue parameter.

    The @NextID returned is always the first ID of a reserved block of numbers. The reserved

    block size defaults to 1.

    Usage:

    EXEC @return = dbo.GetNextID @KeyID,@IncrementValue,@NextID=@NextID OUTPUT

    Outputs:

    1. Returns a -1 if error and 0 if success.

    2. @NextID will be a -1 if an error occured. Otherwise, it will contain the first

    NextID of the requested block of NextID's.

    Notes:

    1. This procedure has been enhanced compared to the original...

    a. The UPDATE statement sets both the @NextID variable and the NextID column in the

    NextID table eliminating the need for a separate SELECT from NextID after the

    UPDATE.

    b. Because of (1.a) above, there is no longer a need for a transaction. If the

    UPDATE didn't work, there is no need for a ROLLBACK because nothing was updated.

    c. Previous error handling did not correctly return the invalid KeyID if present.

    d. A test has been added to ensure a negative value for @IncrementValue was not

    passed in.

    e. A test to ensure that @NextID was correctly updated has been added.

    f. Repairs to the previous error routines have been made so that the values returned

    to @@ERROR and @@ROWCOUNT are correctly used by more than one statement.

    Revisions:

    REV 01 - 01 Mar 2005 - Kalpa Shah, Jeff Moden --Rewrite original

    REV 02 - 06 Feb 2010 - Jeff Moden -- Removed all company references

    ****************************************************************************************/

    --=======================================================================================

    -- Define the I/O parameters used by this procedure

    --=======================================================================================

    --===== Declare the passed parameters

    @KeyID INTEGER, --Identifies table to get the NextID for

    @IncrementValue INTEGER = 1, --Number of NextIDs to "reserve"

    @NextID INTEGER OUTPUT --Returns start # of block of IDs

    AS

    --=======================================================================================

    -- Main body of procedure

    --=======================================================================================

    --===== Suppress auto-display of row counts for appearance and speed

    SET NOCOUNT ON

    --===== Declare variables local to the loop

    DECLARE@MyError INTEGER --Holds @@ERROR for additional processing

    DECLARE @ErrMessage VARCHAR(100) --Holds calculated error messages because RaisError

    --cannot calulate messages on the fly.

    DECLARE @MyRowCount INTEGER --Hold @@ROWCOUNT for additional processing

    --===== Preset @NextID to an error condition

    SET @NextID = -1 --Defaults don't work consistently on OUTPUT parameters

    --===== If the increment is not greater than zero, raise and error and exit immediately

    IF @IncrementValue <= 0

    BEGIN --Start of error processing

    --===== Process errors (RaisError cannot do calcs for error message)

    SET @ErrMessage = 'The NextID row could not be updated. '

    + 'Increment was set to '

    + CONVERT(VARCHAR(11),@IncrementValue) + '.'

    RAISERROR (@ErrMessage,1,1)

    RETURN -1 --Returns an error indication to calling procedure

    END --End of error processing

    --===== Update the correct NextID row according to the KeyID passed in.

    -- Sets @NextID and the column to the previous value + the increment

    -- simultaneously so we don't need to read from the NextID table to

    -- get the value of @NextID in the following steps.

    UPDATE dbo.NextID WITH (UPDLOCK)

    SET @NextID = NextID = NextID + @IncrementValue

    WHERE KeyID = @KeyID

    -- Get the error value and rowcount

    SELECT @MyError = @@ERROR, @MyRowCount = @@ROWCOUNT

    --===== Check for errors, a rowcount of 1, and a non-default value for @NextID

    IF @MyError <> 0 --An error did occur

    OR @MyRowCount <> 1 --The row was not updated

    OR @NextID = -1 --A new value for @NextID was not returned

    BEGIN --Start of error processing

    --===== Process errors (RaisError cannot do calcs for error message)

    IF @MyError <> 0 --Error occured

    SET @ErrMessage = 'The NextID row could not be updated.'

    ELSE --1 row or @NextID was not not updated

    SET @ErrMessage = 'The NextID row could not be updated. KeyID '

    + CONVERT(VARCHAR(11),@KeyID)

    + ' may not exist.'

    RAISERROR (@ErrMessage,1,1)

    RETURN -1 --Returns an error indication to calling procedure

    END --End of error processing

    --===== Calculate and return the first number in the block of reserved NextID's

    -- to the @NextID output parameter

    SELECT @NextID = @NextID - @IncrementValue

    --===== Return a "success" indication to the calling procedure

    RETURN 0

    GO

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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