Managing column_id when using COLUMNS-UPDATED

  • jburkman

    Right there with Babe

    Points: 768

    G'morn. I use COLUMNS-UPDATED on a couple of audit-type triggers to avoid having certain columns trigger the..erm..trigger. Working on my local db, I added a new column that I want mask. I look up the column_id. 37. Great. I tweak a few things, dropping then adding that column until I like my code. Then I notice that the column_id has increased with each drop/add. Now it is at 40.

    Not a problem. Except my COLUMNS-UPDATED mask for column_id 40 won't be applicable to production, where the added column will have an ID of 37.

    So. Bleary-eyed early morning complexities make me ramble. But can I:

    "reset" the column_id increment of a table, so it will be sure to always assign ([last existing column_id + 1]), OR

    assign the column_id at the time I create a column?

    *slogs off in search of more coffee*

  • Michael Earl-395764

    SSC Guru

    Points: 53873


    You may or may not have noticed that when you modify a table from Management Studio (or EM for that matter), it often creates a new table and copies over all of your data. This is primarily so it can manage ordinal positions and column_id's because with alter statements you have no control over them.

    So, I suggest you alter your table once through management studio and save the script rather than running it to see what you get. This script will fix your problem.

  • jburkman

    Right there with Babe

    Points: 768

    I've not noticed the copying over of the table on when adding a column. However, your comment (and some coffee) did get me past my brainblock. A quick "INSERT INTO" and a name change and my local table is back to incrementing the column_id from the last existing column.

    But I still wonder how to "reset" that, to lose the excess "used but no longer there" column_ids.

    Curiosity. Me and cats. Analogous results.

  • Jeff Moden

    SSC Guru

    Points: 996858

    Like Michael said... can't be done unless you drop/recreate the table.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

Viewing 4 posts - 1 through 4 (of 4 total)

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