unique column in 2 tables - interesting problem

  • Heh.... agreed... poor ol' Adam already admitted that the whacko that designed this headed for the hills a long time ago... Who would do this?  I can (possibly) understand needing to insert into two tables because of out of control row lengths > 8060, but to use staggered identities?  I don't think that makes much sense to anyone including Adam.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Maybe you would do this if you had 2 different product lines, but needed to have unique product numbers between the two lines?  And the developer did not know how to normalize the data.

  • A very elegant solution, but what a headache if anything uses the existing IDs! I run a website and Google indexing dynamic pages with the ID in the url springs to mind. Whoops, there goes your organics.

    Its not foolproof but an in place fix would be to cleanse the data to fix existing duplicates then create a scalar function that returns the max value from a union of the two ID columns (+1 of course) To boot, your IDs will be genuinely contiguous.

    Simply call your function whenever you need a new ID and you're done. No more dupes as long as nobody tries to manually update the field with an existing key.

     

    Rawly

     

     

  • Couldn't you then run into the problem of 2 trans beginning at the same time, each getting the same new id number?

Viewing 4 posts - 16 through 18 (of 18 total)

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