Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: Generating specific range of ID's for table...

    Hey everyone,

    I am after a way to generate a specific range of ID's to use when creating a new ID.

    I need the ID's to be within this specific range:
    111111 - 119999
    121111 - 129999
    131111 - 139999
    ...
    ...
    191111 - 199999

    That is the requirement of the contract, so I am unable to change this...

    I realise this is probably not 100% sql server code, and generating those ID's would not be hard in c# with a specific algorithm....

    What I am after is a way to ensure data integrity of this method...

    I ie, how to use my algothrym to properly generate the ID.

    ie, do I get the ID of the last inserted ID, ie 111115 and then increment it in my code, then attempt to insert a row using that data. what happens if 2 users are created almost instantaniously...do I have a catch block in my code, and respond to a "unique Item already exists" by incrementing again and trying to re-insert the row?

    any help or pointers on this would be very helpful,

    Cheers

  2. #2
    Join Date
    Feb 2007
    Posts
    62

    You can't do it well

    This is a dumb requirement. The reason why is that in order to achieve this you will have to completely serialise your database, i.e. wait for each and every transaction to complete before you can start the next one. How else will you know what the next number is going to be unless you have committed or cancelled the one before? This will grind your transaction processing ability to a halt.
    I suggest you use identities and then simulate the actual ID using ROW_NUMBER() when you select your results and/or have a job to allocate the sequential IDs to a permanent column. You cannot win any other way

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •