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:
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?
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,
RETURNS @cols TABLE (col sysname NOT NULL PRIMARY KEY) AS
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)
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
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".