Generate Next Numbers with SQL Server

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists




    My Blog: http://dineshasanka.spaces.live.com/

  • dogeth

    Mr or Mrs. 500

    Points: 556

    I looked at your proc, and have a few concerns.

    1) You state that it handles the multi-user environment by generating and updating the next number in the same stored procedure.  Incorrect.  Your not using transactions.  Under a heavy load it is possible for two users to select from the table before either updates it

    2) Use of dynamic sql.  Come on, do you really need to use it here!

    Sorry, but I think that there is plenty of room for improvment here.

     


    Cheers,

    Kevin

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Thankx for the reply

    1. My concern is not about the accessing. but getting same no for both users.

    Say User A Read and get number 15 before updating it user B also gets 15

    I want to stop that.

    2. What do u mean by dynamic sql.

    and do u have any other way of doing this. because I'm looking for this kind of solutions

    Thankx again for the reply




    My Blog: http://dineshasanka.spaces.live.com/

  • rschaeferhig

    SSCrazy Eights

    Points: 8776

    This part is Dynamic SQL:

    Select @sql = "Update " + @Tablename + " Set LastNumber ="

    + Cast(@LastNumber as varchar(10)) +

    " Where CODE ='" + @secondType + "'"

    execute (@sql)

     

     

  • dogeth

    Mr or Mrs. 500

    Points: 556

    You need to have the "selecting, incrementing & updating" process happen for one person at a time.  You can use a transaction and locking to do this, for example...

     --------------------------------------------------------------------------------------

     begin transaction

     --------------------------------------------------------------------------------------

     --Lock the Table

     select @RowCount = count(*) from SEQ_NO with (TABLOCKX)

     --Get the Value

     Select @LastNumber = LastNumber From SEQ_CHAN Where CODE = @secondType

     --Increment the Value

        Select @LastNumber = @LastNumber + 1

     --Update the Value

        Update SEQ_NO Set LastNumber = @LastNumber Where CODE = @secondType

     --------------------------------------------------------------------------------------

     commit transaction

     --------------------------------------------------------------------------------------

    Alternatively, without using a transaction at all, add the old value of the "Next Number" to the where clause of the update and check the rowcount.  If you haven't updated any rows then you need to get the next number.

     

     


    Cheers,

    Kevin

  • Dinesh Asanka

    SSChampion

    Points: 11058

    My argument is if both users are using same type. they will get same number there can be duplicate number unless u update it just after reading.

     

     




    My Blog: http://dineshasanka.spaces.live.com/

  • dogeth

    Mr or Mrs. 500

    Points: 556

    What?  Sorry, but I'm not sure of your requirement.

    Are you saying that if two users are using the same type, it is OK for them to have duplicate numbers?


    Cheers,

    Kevin

  • Dinesh Asanka

    SSChampion

    Points: 11058

    NO SIR,

    If I didn't update just after reading there is chance of getting same number for both records.

    To avoid that I am updating the Next number Just after reading.

     

     




    My Blog: http://dineshasanka.spaces.live.com/

  • dogeth

    Mr or Mrs. 500

    Points: 556

    By putting the update straight after the read is enough.  You need to do more.  Check this script out to illustrate my point...

    use tempdb

    create table Numbers (LastNumber int not null, primary key(LastNumber))

    insert into Numbers values (0)

    Now create 2 connections to the database and run the following script on each one at the same time.  (This will simulate 20000 requests for next number in a short period of time)

    set nocount on

    declare @LastNumber int

    declare @NewNumber int

    declare @Count int

    declare @Index int

    select @Count=10000, @Index=1

    while (@Index <= @Count)

    begin

        Select @LastNumber = LastNumber From Numbers

        Select @NewNumber = @LastNumber + 1

        Update Numbers Set LastNumber = @NewNumber

        select @Index= @Index + 1

    end

    Select LastNumber From Numbers

     

    When I run this on my local tempdb, I get the result as 17209. This means that there were 2791 duplicate next numbers generated!  Obviously you may not have the demand of 20000 instantaneous requests, but it only needs to happen once to corrupt your data integrity.

    Now change the script to either use transactions, or include the LastNumber in a where clause of the update statement (as mentioned in my previous post) to correct the problem.

     


    Cheers,

    Kevin

  • Dinesh Asanka

    SSChampion

    Points: 11058

    I got your point now

    Thankx

    I thought u were saying some error in my method.

    your request is an improvement which is welcome.

    And for second question

     y are against with dynamic SQL

    in my case I have to use it. What are the altenatives u have.

     

    Thankx again for the reply




    My Blog: http://dineshasanka.spaces.live.com/

  • dogeth

    Mr or Mrs. 500

    Points: 556

    I can't offer you an alternative, as I don't know your business requirements.

    I don't know how flexible your system needs to be, but when I saw that you were getting the tablename from the next number table, I just didn't see the point.  Why the extra table in the first place?


    Cheers,

    Kevin

  • jcool

    SSC Eights!

    Points: 863

    While briefly reviewing your code I saw something funny. You are using varchar for date variable. This may work, but you mus be aware that SQL Server has to do an implicit conversion. Every time you fill getdate() to varchar it is converted according to local server settings. When you use YEAR(), it is converted back to datetime.

    Much simpler solution: use datetime type for date varable, even as stored procedure parameter. You can call the procedure and send it specially formatted string that will be converted to datetime on entry. This format is called XML Date (see BOL) and it looks like this: yyyy-MM-ddThh:mm:ss  (2004-04-05T00:00:00): This format is correctly converted to the right datetime no matter how local language, dateformat on the server is set.

    You should also use transaction to isolate what you are doing. And very important is explicit table or at least record locking to prevent some other process from reading the last number before you updated it. Follow the examples mentioned by others.

    Otherwise, how can you be sure, vou aren't getting duplicate numbers? Do you have a unique constraint on the numbering field?

     

    Best regards, Jani

     

  • Antares686

    SSC Guru

    Points: 125444

    Also note you can do assignments like so.

    update tblName set @var = col = col + 1

    Which means you can read and write the data at the same time. This will help prevent the chance that more than one person can read the same value since you can do the set for all of your columns and variables at the same time on your numbering table.

    Use this every so often myself.

    The problem with the multiple selects as pointed out is unless you hold locks during the whole process you can potentially have more than one person get the same value in a multiuser process.

    The downside to holding the lock thou is that it creates a slowdown in the number of items you can process at one time.

  • jopro

    SSC Enthusiast

    Points: 161

    --Lock the table;

    DECLARE @integer_variable int

    IF @@trancount = 0

    Begin Transaction;

    -- Perform Update

    Update table

    Set Tablename.Seqnr = Tablename.Seqnr + 1

    Where Blabla

    --Perform Errorhandling !!! (no code inhere)

    -- Perform SELECT

    Select seqnr

    FROM table

    Where blabla.

    -- Perform Errorhandling !!!

    If okay

    begin

      COMMIT Transaction;

      return;

    end

    IF not okay

    begin

      ROLLBACK TRANSACTION;

      return(ERROR)

    END

  • johnsonj

    SSCrazy

    Points: 2159

    I agree with the locking -- you have to use transaction.  However, sometimes dynamic SQL is necessary -- it depends on the situation.

    However, the question I have is:  Did the customer fully understand the cost associated with the inflexibility of changing their current number system?

    I know most customers hate change, but sometimes it is necessary as the benefit does not outweight the cost.  For example, I ran into the same type of problem with numbering while a project manager for an implementation.  The users did not want to change, but when explained to them the cost of modification in relation to what they would get, they decided to change.

    Had I not insisted to the General Manager that the gain was not worth the cost, I would have been negligent in my job.  Even as a consultant, I feel that we have to point out the best possible solution to the management in a way that focuses on a cost-to-benefit ratio.  Even though this may lose you some money, I believe it promotes positive attitudes between the parties and potentially more income in the long run.

     

    Regards,


    Joe Johnson
    NETDIO,LLC.

Viewing 15 posts - 1 through 15 (of 23 total)

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