March 3, 2016 at 5:24 pm
Hi All
I want to assign one unique id to each row in a table and the range is from 1 to 99999. there will be a flag for active inactive as well so if a row gets inactive then that respective unique id can be used for other rows as well. and if there is no inactive record then the next row will get the max(id) + 1. if we reach 99999 then it will again start with the least inactive id. can you please suggest the best way forward for this ?
Many thanks in adavnce
March 3, 2016 at 5:36 pm
bismsit.29 (3/3/2016)
Hi AllI want to assign one unique id to each row in a table and the range is from 1 to 99999. there will be a flag for active inactive as well so if a row gets inactive then that respective unique id can be used for other rows as well. and if there is no inactive record then the next row will get the max(id) + 1. if we reach 99999 then it will again start with the least inactive id. can you please suggest the best way forward for this ?
Many thanks in adavnce
Hello and welcome to theSQLServerCentral.
This shouldn't be that difficult at all (I could put something together but I'm not at a PC at the moment). First, why do you want to re-use these IDs? This does not sound like a good design.
-- Itzik Ben-Gan 2001
March 3, 2016 at 6:07 pm
Hi Alan,
Thank you very much for replying back so early.
Its sort of a business requirement that's why i want to reuse them also this value can hold till 99999 which is why i need to reuse any inactive ids. hope that clarifies the doubt.
with regards,
March 3, 2016 at 6:22 pm
bismsit.29 (3/3/2016)
Hi AllI want to assign one unique id to each row in a table and the range is from 1 to 99999. there will be a flag for active inactive as well so if a row gets inactive then that respective unique id can be used for other rows as well. and if there is no inactive record then the next row will get the max(id) + 1. if we reach 99999 then it will again start with the least inactive id. can you please suggest the [font="Arial Black"]best [/font]way forward for this ?
Many thanks in adavnce
Yes... If the unique IDs are important (and they are be because you want them unique), then don't do this. It will be a world of hurt forever. Reusable identifiers are a source of "Death by SQL". as is limiting the number of IDs.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2016 at 6:32 pm
I'm with Jeff as usual on questioning the design decision/need here. But what you ask for seems tailor-made for SEQUENCEs, which are available in SQL Server 2012+.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 3, 2016 at 6:33 pm
understands the suggestions very much. Can someone of you please en-light me a proper solution for this then ? only bit problematic is - the table will have new rows added daily and my limit of ids is 99999 only.
many thanks for all of you inputs
March 3, 2016 at 6:38 pm
in case of sql server 2008 can there be a solution for this ? i'm new to sql server central so might not have given the sql version i'v to use here. thanks for your help so far
March 4, 2016 at 1:50 am
HI Alan,
Can you please let me know if you got time to sort this out ? I might need a solution which is applicable for sql server 2008 r2.
best regards,
March 4, 2016 at 2:06 am
I agree with others that this is a bad idea. Would you be able to tell us more about the reason for the requirement, please - we may be able to suggest something better?
If you have no alternative but to go ahead with this, you'll need to provide more details. Is this a new table, or does it already exist with data in it? If it exists, does it already have the Active column? How will you decide whether a row is active or inactive? Can a row change from inactive to active, and if so what happens if that causes a duplicate ID? What will you do if there are already 99,999 active rows and it comes to time to insert the 100,000th?
John
March 4, 2016 at 5:09 am
sure john i will let you know all in detail. i would look forward for your suggestion in this.
this is a new table which will get offer feed from a different db. the offers will have inactive date and if inactive date is less than today then the row will be inactive. i need to assign each row one unique id but the id cant be more than 99999 - because of which I'm thinking to reuse the ids. it will be a highly unlikely scenario when there could be more than 99999 active offers in that so we can reuse the id for the inactive offer to the newly inserted active offers. if the max id is 99999 then it might start with the least inactive id.
hope this covers all questions. looking forward to your expert opinion.
March 4, 2016 at 5:58 am
I will say again that this is a perfect scenario for a SEQUENCE. It offers everything you are asking for and is very easy to use. Look it up in Books Online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 4, 2016 at 7:00 am
Hi Kevin
I had mentioned this previously that I had mistakenly put this question in this blog as I needed the solution for SQL 2008 R2 which unfortunately doesn't support sequence. Apologies for this inconvenience.
If you could give us a solution for SQL 2008 please.
Best regards,
Biswajit
March 4, 2016 at 7:21 am
bismsit.29 (3/4/2016)
sure john i will let you know all in detail. i would look forward for your suggestion in this.this is a new table which will get offer feed from a different db. the offers will have inactive date and if inactive date is less than today then the row will be inactive. i need to assign each row one unique id but the id cant be more than 99999 - because of which I'm thinking to reuse the ids. it will be a highly unlikely scenario when there could be more than 99999 active offers in that so we can reuse the id for the inactive offer to the newly inserted active offers. if the max id is 99999 then it might start with the least inactive id.
hope this covers all questions. looking forward to your expert opinion.
We actually understood that part from your previous descriptions. What people are asking is why has someone limited the offer number to only 99,999 instead of just letting the number increase? It creates many problems like needing to have a "trick" Primary Key to prevent duplication and makes it so that every query against history will require the same kinds of SQL prestidigitation. It would be much better and save a huge amount on development and testing time not to mention the time it will take to do any kind of troubleshooting, rework, etc, not to mention reducing the huge likelihood of deadlocks and/or severe blocking to near zero if you just used something like an ever increasing IDENTITY column or dedicated SEQUENCE (IDENTITY column is the easiest and most reliable).
I know you didn't design it that way and no right-minded Developer, DBA, or system Architect would either. For the record, the people that designed this travesty are out of their cotton-picking-minds.
That, notwithstanding, Developers are frequently tasked to accomplish such lunacy as a part of their jobs and they sometimes have little say so. [font="Arial Black"]With that in mind, lets see if we can help.
[/font]
[font="Arial Black"]There are actually several ways to accomplish this but,[/font] to make it perform as best as it can and have a decent chance of reducing the deadlocks that will likely occur, we need to know how often the table is input to and how many rows per literal INSERT there will be. Because of the relatively small size of the table, we also don't want to just assume that it will be a very low INSERT frequency by one row at a time no matter how likely we might believe that to be the case.
We also need you to post the CREATE TABLE statement, the CREAT INDEX statements, and any CREATE TRIGGER statements along with confirmation of whether or not this will be low frequency, single row inserts or what. Posting some readily consumable actual test data using the methods outlined in the article at the first link under "Helpful Links" in my signature line below would also be a huge help in us helping you.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2016 at 9:23 am
Jeff Moden (3/4/2016)
bismsit.29 (3/4/2016)
sure john i will let you know all in detail. i would look forward for your suggestion in this.this is a new table which will get offer feed from a different db. the offers will have inactive date and if inactive date is less than today then the row will be inactive. i need to assign each row one unique id but the id cant be more than 99999 - because of which I'm thinking to reuse the ids. it will be a highly unlikely scenario when there could be more than 99999 active offers in that so we can reuse the id for the inactive offer to the newly inserted active offers. if the max id is 99999 then it might start with the least inactive id.
hope this covers all questions. looking forward to your expert opinion.
We actually understood that part from your previous descriptions. What people are asking is why has someone limited the offer number to only 99,999 instead of just letting the number increase? It creates many problems like needing to have a "trick" Primary Key to prevent duplication and makes it so that every query against history will require the same kinds of SQL prestidigitation. It would be much better and save a huge amount on development and testing time not to mention the time it will take to do any kind of troubleshooting, rework, etc, not to mention reducing the huge likelihood of deadlocks and/or severe blocking to near zero if you just used something like an ever increasing IDENTITY column or dedicated SEQUENCE (IDENTITY column is the easiest and most reliable).
I know you didn't design it that way and no right-minded Developer, DBA, or system Architect would either. For the record, the people that designed this travesty are out of their cotton-picking-minds.
In a previous company, a similar restriction occurred. There couldn't be more than 9,999 branches. This was a federal regulation because there wouldn't be a bank with that much branches. However, the moroffs that defined that regulation, never thought that a closed branch should retain its number. The assignment of these numbers was assigned to a special team and it didn't have an intensive load.
To me, it seems like a SCD type 2, meaning that this column shouldn't be the PK of the table. I'll leave this to others with experience on higher loads as I can imagine lots of ways to get this wrong or inefficient.
March 6, 2016 at 2:56 pm
bismsit.29 (3/3/2016)
Hi AllI want to assign one unique id to each row in a table and the range is from 1 to 99999. there will be a flag for active inactive as well so if a row gets inactive then that respective unique id can be used for other rows as well. and if there is no inactive record then the next row will get the max(id) + 1. if we reach 99999 then it will again start with the least inactive id. can you please suggest the best way forward for this ?
Many thanks in adavnce
Scenario 1.
You've reached the number 50000. At the time (your last assigned active ID = 50000) you have deactivated numbers from 20001 to 40000.
Which should be you next sequence number?
Scenario 2.
You've reached number 99999. by that time you had already deactivated numbers from 0 to 40000.
So you've started over from 0 and eventually reached the number 20000. At the time (your last assigned ID = 20000 ) you deactivate the numbers from 50000 to 99999.
What should be your next sequence?
Please note - both scenarios bring to the same set of "active" ID's:
0-20000
40001-50000
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply