September 21, 2011 at 12:34 pm
I have a table where I need to generate a unique (non-duplicate) integer. I would use IDENTITY, but this integer needs to be updated to a new integer if another field changes (PART_NUMBER).
Table1
ID (GUID)
PART_NUMBER (varchar)
NEW_COLUMN (integer) - unique
The only way I can think to do it is to have another temporary table (Table2) with an IDENTITY field. I'd have a trigger set up on Table1 so that whenever an insert is done or the PART_NUMBER is updated then a new record is created on Table2 and that value is set to the NEW_COLUMN field on Table1. It seems like a hack way of doing it though IMO.
Any other ideas?
Thanks!
September 21, 2011 at 12:39 pm
Can you have 1 column be an identity column, another be a flag or something, and a final one be a calculated column based on the identity and the value in the flag?
Jared
Jared
CE - Microsoft
September 21, 2011 at 12:44 pm
Will other tables reference the ID column in this table? If so, updating it will break those references.
What you're probably better off doing is, if the Part Number column is updated, have an Instead Of Update trigger on the table, and insert a new row with a new ID and the new Part Number, instead of updating the existing row.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 21, 2011 at 12:58 pm
I think we really need more information here... What exactly are you trying to do; i.e. if a new row is inserted, what changes with an old row? Also, if a part number is changed, why does this new field change? On the surface it seems like a bad design, but that's probably because I don't understand what you are trying to do.
Thanks,
Jared
Jared
CE - Microsoft
September 21, 2011 at 1:56 pm
jared-709193 (9/21/2011)
I think we really need more information here... What exactly are you trying to do; i.e. if a new row is inserted, what changes with an old row? Also, if a part number is changed, why does this new field change? On the surface it seems like a bad design, but that's probably because I don't understand what you are trying to do.Thanks,
Jared
I was trying to make it as simple as possible, but I'll give you some more nuts and bolts of it.
On the table there's a Serial Number field, but it's nullable. For reporting purposes, there always needs to be a serial number of some kind. So I was thinking of having another temporary Serial Number that's always filled in. It doesn't really matter what it is as long as there's no duplicates, so I thought an incremental integer would be easiest. On the report, if there's no SERIAL_NUMBER then I'd populate that field with the TEMP_SERIAL_NUMBER value.
And like I said above, if the PART_NUMBER changes then the TEMP_SERIAL_NUMBER needs to be updated with another non-duplicate number.
Table1
ID (GUID)
PART_NUMBER (varchar)
SERIAL_NUMBER (varchar)
TEMP_SERIAL_NUMBER (integer)
I appreciate you taking the time to help with this. Thanks.
September 21, 2011 at 1:58 pm
GSquared (9/21/2011)
Will other tables reference the ID column in this table? If so, updating it will break those references.What you're probably better off doing is, if the Part Number column is updated, have an Instead Of Update trigger on the table, and insert a new row with a new ID and the new Part Number, instead of updating the existing row.
I don't want to change the ID (GUID) column.
There's a lot more going on with this table (like 30 fields and 6 joins and a lot of business logic) so I can't create a new row.
September 21, 2011 at 2:53 pm
sjusko (9/21/2011)
GSquared (9/21/2011)
Will other tables reference the ID column in this table? If so, updating it will break those references.What you're probably better off doing is, if the Part Number column is updated, have an Instead Of Update trigger on the table, and insert a new row with a new ID and the new Part Number, instead of updating the existing row.
I don't want to change the ID (GUID) column.
There's a lot more going on with this table (like 30 fields and 6 joins and a lot of business logic) so I can't create a new row.
Ah. I misunderstood which column you wanted to change.
With the structure you have, I'd avoid an identity column.
How heavy are the inserts/udpates/deletes in that table? Lots of concurrency or very little?
Most likely, if the I/U/D traffic and concurrency aren't too bad, you could set up a single-row table with an integer value in it. Query and increment that, using TABLOCKX to prevent concurrency issues, to get your virtual Serial Number value. You'll have to make sure the code doing this doesn't present deadlock issues, of course.
Look around for articles on "custom ID value in SQL Server", you'll find the Do and Don't lists for that kind of thing. See if it sounds like it will work for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 21, 2011 at 3:26 pm
Hmm... What I would do is create another column and make it an identity insert starting at 10000 or something... You could even make this your primary key and get rid of the GUID. Then, for the serial number, default it to CAST(ID AS VARCHAR(10)) + PARTNUM... or the other way around. This way it will always be unique and updatable. Does this work for your situation?
Thanks,
Jared
Jared
CE - Microsoft
September 21, 2011 at 3:31 pm
Another thought is to convert the GETDATE() of insertion to UNIX time (I forgot what it is called) by doing this:
SELECT DATEDIFF(ss,'1970-01-01',GETDATE())
and concatenating that with the part num as default, depending on your amount and speed of inserts.
Jared
Jared
CE - Microsoft
September 22, 2011 at 6:32 am
GSquared (9/21/2011)
sjusko (9/21/2011)
GSquared (9/21/2011)
Will other tables reference the ID column in this table? If so, updating it will break those references.What you're probably better off doing is, if the Part Number column is updated, have an Instead Of Update trigger on the table, and insert a new row with a new ID and the new Part Number, instead of updating the existing row.
I don't want to change the ID (GUID) column.
There's a lot more going on with this table (like 30 fields and 6 joins and a lot of business logic) so I can't create a new row.
Ah. I misunderstood which column you wanted to change.
With the structure you have, I'd avoid an identity column.
How heavy are the inserts/udpates/deletes in that table? Lots of concurrency or very little?
Most likely, if the I/U/D traffic and concurrency aren't too bad, you could set up a single-row table with an integer value in it. Query and increment that, using TABLOCKX to prevent concurrency issues, to get your virtual Serial Number value. You'll have to make sure the code doing this doesn't present deadlock issues, of course.
Look around for articles on "custom ID value in SQL Server", you'll find the Do and Don't lists for that kind of thing. See if it sounds like it will work for you.
Very little concurrency issues - at most only a few people will be I/U/D at one time, but usually one at a time.
Thanks a lot for the info! I'm going to go this route. I didn't even think of the deadlock issue, so thanks for bringing that up.
I found an article here: http://www.sqlmag.com/article/sql-server/deadlocks-with-custom-sequence
I'm going to use TABLOCKX as you suggested though.
September 22, 2011 at 6:35 am
jared-709193 (9/21/2011)
Hmm... What I would do is create another column and make it an identity insert starting at 10000 or something... You could even make this your primary key and get rid of the GUID. Then, for the serial number, default it to CAST(ID AS VARCHAR(10)) + PARTNUM... or the other way around. This way it will always be unique and updatable. Does this work for your situation?Thanks,
Jared
jared-709193 (9/21/2011)
Another thought is to convert the GETDATE() of insertion to UNIX time (I forgot what it is called) by doing this:
SELECT DATEDIFF(ss,'1970-01-01',GETDATE())and concatenating that with the part num as default, depending on your amount and speed of inserts.
Jared
The GUID is needed because of data coming from different areas so that's a no go. Also, ultimately I'm going to be concatenating the Serial Number and Part Number for a report, so having the Part Number in the end result twice wouldn't work.
The datediff is a great solution I didn't think of. I'll look into that if the other custom sequence table doesn't work out for some reason.
Thanks a lot guys!
-Scott
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply