COLUMNS_UPDATED() and Phantom Fields

Question

Post reply

COLUMNS_UPDATED() and Phantom Fields

greg.farnan

SSC Enthusiast

Points: 193
More actions
March 12, 2012 at 6:10 am

#252883

Hi
I'm using COLUMNS_UPDATED() in an audit trigger and have found that it appears to retain a slot in the returned bitmask for a long since deleted field.
I'm using the ORDINAL_POSITION field from INFORMATION_SCHEMA.COLUMNS to get the column indicies for the table then checking the bitmask for each index. We noticed that one column was always being skipped by the trigger - it turned out to be a 'phantom' field being included in the bitmask.
A check against sys.columns shows a jump in the column_id values where the phantom field is appearing. A chat with my colleagues confirms a old password field was dumped to appease the auditors (it wan't being used).
MSDN warns column IDs may not be sequential: http://msdn.microsoft.com/en-us/library/ms176106.aspx
So my question is, where is COLUMNS_UPDATED getting it's schema information for the table? I need to use the same source to safely enumerate it's bits.
Any response is greatly appreciated, this trigger is designed to be as generic as possible so would like to avoid hard coding fixes on individual tables which would also mean reacting to future changes that drop fields.
Dropping and recreating the table feels a little excessive. I'm supposed to have minimal impact on the DBs I'm applying this to.
Many thanks
Greg

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

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Hi Greg,

From Books Online:

In SQL Server 2008, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example.

SELECT TABLE_NAME, COLUMN_NAME,

COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),

COLUMN_NAME, 'ColumnID') AS COLUMN_ID

FROM AdventureWorks2008R2.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Contact';

Although as far as I can tell, that just returns the ColumnId value from the sys.columns view, so it could be easier to query that directly!

Hope that helps 🙂

Gaz

greg.farnan SSC Enthusiast Points: 193 More actions · Answer 2

greg.farnan

SSC Enthusiast

Points: 193

March 12, 2012 at 6:39 am

#1457796

Perfect Gaz, Thanks!

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Gazareth

One Orange Chip

Points: 27737

March 12, 2012 at 8:48 am

#1457899

No problem!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

If you use, sys.columns, you don't have to worry about such a thing.

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

greg.farnan SSC Enthusiast Points: 193 More actions · Answer 5

Thanks Jeff, that's the approach I went with. I was really just hoping for confirmation that the COLUMNS_UPDATED() function works off the column_id field of sys.columns. Gaz's post led me to the following two articles (would be nice if this was stated more explicitly somewhere);

http://msdn.microsoft.com/en-us/library/ms186329.aspx

http://msdn.microsoft.com/en-us/library/ms174968.aspx

Greg

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

It does seem odd that the example adds a layer of abstraction by suggesting using the COLUMNPROPERTY function against INFORMATION_SCHEMA tables, rather than going directly to the sys.columns view. ANSI SQL compatability?