ID generated for group of field

  • a_ud

    SSCrazy

    Points: 2457

    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.
    • 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).
    • 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

  • sgmunson

    SSC Guru

    Points: 110433

    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)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Viewing 2 posts - 1 through 2 (of 2 total)

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