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