Question

Buggerlugs9 on Wed, 12 Oct 2016 10:29:34


There is a table in our database with 15 columns and I am using the COLUMNS_UPDATED function in an update trigger to work out which columns have been updated.  Everything was working fine until a couple of columns were dropped from the table.  When this occurred, the COLUMNS_UPDATED function continued to return a value as though the two dropped columns still existed.  This is further complicated by the fact that in new databases, which have been built from scratch, the COLUMNS_UPDATED function returns the expected value.  The table below should help to clarify what I am talking about:

Ordinal Column Name Expected Actual
1 AlertID 0x0100 0x010000
2 AlertTypeID 0x0200 0x020000
3 AlertContentID 0x0400 0x040000
4 IsHTML 0x0800 0x200000
5 Name 0x1000 0x400000
6 Description 0x2000 0x800000
7 Subject 0x4000 0x000100
8 Content 0x8000 0x000200
9 Consolidate 0x0001 0x000400
10 RunOnce 0x0002 0x000800
11 LastRunTime 0x0004 0x001000
12 LastSendTime 0x0008 0x002000
13 LastSendCount 0x0010 0x004000
14 Created 0x0020 0x008000
15 LastUpdated 0x0040 0x000001

Looking at the MSDN documentation for the COLUMNS_UPDATED function (msdn.microsoft.com/en-us/library/ms186329.aspx), it seems that there is some awareness of this issue.  This article states: "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".

Indeed, if I reference the ColumnID property, columns 4 - 15 in the table above are have column IDs 6 - 17.  What is the cause of this issue and is it a defect which will be fixed at some point in the future?



Sponsored



Replies

Erland Sommarskog on Wed, 12 Oct 2016 12:02:07


I would not expect it to change in the future. column_id is not changed when a column is dropped, because it is a metadata-only operation. However, if you rebuild the table, the space is reclaimed. Don't remember for sure about the column_id.

Anyway, colums_updated is very brittle, and I don't like using it directly. I tried to avoid it entirely for many years, but eventually I had a case where I needed to check that certain columns were not touched at all. I wrote a table-valued function to can call like this:

   SELECT col FROM updated_cols(@@procid, columns_updated())

And the result set will hold all columns updated.

Caveat: the user must have at least VIEW DEFINITION on the table! If this is not the case, you need to sign the function with a certificate which has been granted VIEW DEFINITION on database level.

CREATE FUNCTION updated_cols (@trigger_id      int,
                              @columns_updated varbinary(200))
 RETURNS @cols TABLE (col sysname NOT NULL PRIMARY KEY) AS
BEGIN
   INSERT @cols(col)
     SELECT c.name
     FROM   sys.columns c
     JOIN   sys.triggers t ON c.object_id = t.parent_id
     WHERE  t.object_id = @trigger_id
       -- Columns_updated() returns a bit mask which holds the updated column,
       -- here below unencoded.
       AND  substring(@columns_updated, (c.column_id - 1)/ 8 + 1, 1) &
            power(2, (c.column_id - 1) % 8) =  power(2, (c.column_id - 1) % 8)
    RETURN
END

Buggerlugs9 on Thu, 13 Oct 2016 13:16:20


Thank you Teige, that clarifies things.

;-)

Teige Gao on Tue, 18 Oct 2016 02:18:39


Hi Buggerlugs9,

It is pleasant that your problem has been solved, does the post given from Erland help you? If you are satisfied with this solution, could you please mark his reply as "Answered".

Best Regards,
Teige