Managing column_id when using COLUMNS-UPDATED

Question

Managing column_id when using COLUMNS-UPDATED

jburkman

Right there with Babe

Points: 768
More actions
April 30, 2008 at 8:22 am

#181756

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*

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Um...nope.

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 More actions · Answer 2

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: 1004432 More actions · Answer 3

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.

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