Generate Next Numbers with SQL Server

  • I find this article unreadable.

    The very wide image causes the text to spread out so far to the right that excessive horizontal scrolling is needed.  So, as you read each line, you have to scroll back and forth.

    Extremely poor layout.

    Did you review the layout before you published it?

  • Jim Underwood ,

    I also found it unreadble. I think this formatting is introduced when publishing the artcle.

    My Blog:

  • I found some different ratings to this articles.

    It will be better to get a feed back rather than just a rating.

    Is there any way that I can see who rated this and what is the ratings.

    My Blog:

  • Hey good idea Antares686

    update tblName set @var = col = col + 1

    I'll definitely use this method from now on.  No explicit transaction, no explicit locking... Thanks!



  • Hay you are doing good job but consider my suggestion also,the discussion is moving away from the topic "Generate Next Numbers with SQL Server"








    Then consider a pattern like YYYY#### means 20040001, 20040002 , ……

    Then Then consider another like YYMM#### means 04030001, 04030002 , ……

    SEQ_RESET filed may take values (‘DAILY,’‘MONTHLY’,’YEARLY’,’NEVER’),


    Then all others are left to your imagination ,

    But I would like to know on your response on my suggestion




  • Consider this approach...

    Three tables with an identity field for each table.

    When you add an invoice, add a record to the invoice table, then use the identity from the invoive table (IDENT_SEED) with your required prefix in the table that you actually want to store the data.

    Same goes for the other two tables.

    When you need to reset your values, a scheduled job truncates the invoice table (that is used to simply generate the identity values) which resets the seed to 1.

    BTW, for the date portion:  RIGHT(YEAR(GETDATE()), 2) + RIGHT('0' + MONTH(GETDATE()), 2) will give you the prefix without having to do the IF statement.


  • Thankx Coutant  and johncyriac 

    Certainly there is space for improvements.

    But I just want to presenmt my idea

    Thankx a lot for ur comments


    My Blog:

  • Why don't you let the database structure handle this with additional fields for date, type of entity (order, invoice, etc.), unique id(generated from SQL Server)?  The unique entity number can then be derived from the elements after the unique id is returned.  Your one-off-approach-for -this-client may prove difficult to maintain and likely to cause issues later on.  What if the table is corrupted, how do you reset the table value?

Viewing 8 posts - 16 through 22 (of 22 total)

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