February 7, 2019 at 10:11 am
Some help needed here.
I would like to use a query to generate an ID linked to a column in table. For clarity, the table has a format similar to this:
ID | Colour | Total ................ AutoID
001 Red 13.3 001
001 Blue 7.5 ................ 001
001 Black 3.0 ................ 001
002 Black 0.0 ................ 002
002 Red 2.0 ................ 002
..........
The restraints are:
There is no option to generate AutoID on a separate operationg, e.g. ID<--> AutoID (lookup table). It has to be generated live in the query.
The original ID needs to be converted (transformed) not to look like the original one; that is, it must not be just a character added, or the string inverted, etc, and of course it must remain unique (it's an index).Only solutions that come to mind are hashing SHA-256 (but this is costy, not sure if SQLServer includes it) or simply an increasing index that goes 001, 002, 003..... which I would prefer.
Can anyone help on how to do the 001, 002 ... live in the query, or propose alternative solutions?
Thank you in advance
February 13, 2019 at 10:31 am
First, using a character based column for any kind of sequential identifier is problematic and costly of its own accord. So you probably need to get away from that idea regardless of anything else. As to creating something unique with a direct correlation to an originally unique value is going to require a 1 to 1 mathematical relationship in order to avoid considerable overhead in deriving the new number. Also, expecting the new number to be an ascending sequential value, such that the same order could be derived from both the new and the old, and yet expecting them to look nothing like each other, is not realistic. The only practical solution is to create a relationship table that does nothing but hold the two primary key values, with an index on each. CLUSTER on the one that gets used most often for looking up the other value. Good luck with the math. One question jumps out at me, though: WHY?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply