COLUMNS_UPDATED() limitations

I see all over Google that TSQL’s COLUMNS_UPDATED() doesn’t work correctly; there are even comments in Books Online that users are having problems with it, saying that it either has problems with SQL 2005 forward (“The COLUMNS_UPDATED() does not work in the procedure called in a trigger since SQL 2005”), or it only works with a certain number of columns (“Doesn't work correctly with tables with more columns”). These are recent posts by the way, both in June 2011.

Actually it is working, and it isn’t.  Run the following script:

SET NOCOUNT ON 
IF OBJECT_ID('TriggerTest') IS NOT NULL
    DROP TABLE TriggerTest;
GO
CREATE TABLE TriggerTest
    (UpdateCol1 int default 0 --1
    ,UpdateCol2 int default 0 --2
    ,UpdateCol3 int default 0 --4
    ,UpdateCol4 int default 0 --8
    ,UpdateCol5 int default 0 --16
    ,UpdateCol6 int default 0 --32
    ,UpdateCol7 int default 0 --64
    ,UpdateCol8 int default 0 --128
    --,UpdateCol9 int default 0 --256
    --,UpdateCol10 int default 0
    --,UpdateCol11 int default 0
    --,UpdateCol12 int default 0
    --,UpdateCol13 int default 0
    --,UpdateCol14 int default 0
    --,UpdateCol15 int default 0
    --,UpdateCol16 int default 0
    --,UpdateCol17 int default 0
    --,UpdateCol18 int default 0
    --,UpdateCol19 int default 0
    --,UpdateCol20 int default 0
    --,UpdateCol21 int default 0
    --,UpdateCol22 int default 0
    --,UpdateCol23 int default 0
    --,UpdateCol24 int default 0
    --,UpdateCol25 int default 0
    --,UpdateCol26 int default 0
    --,UpdateCol27 int default 0
    --,UpdateCol28 int default 0
    --,UpdateCol29 int default 0
    --,UpdateCol30 int default 0    
    );
GO
INSERT TriggerTest (UpdateCol1) VALUES (0)
GO
 
IF OBJECT_ID ('trColumnsUpdateTest', 'tr') IS NOT NULL
    DROP TRIGGER trColumnsUpdateTest;
GO
CREATE TRIGGER trColumnsUpdateTest
ON TriggerTest
FOR UPDATE
AS
BEGIN
    PRINT COLUMNS_UPDATED()
    PRINT CONVERT(INT, COLUMNS_UPDATED())
    
    IF COLUMNS_UPDATED() & 128= 128
        PRINT 'UPDATED'
END
GO

DECLARE @col INT=1
WHILE @col <= 8
BEGIN
    DECLARE @str varchar (max) = 'UPDATE TriggerTest SET UpdateCol'
         +CAST(@col AS varchar (20))+ '=999';
    IF @col=8
        EXEC (@str)    
    SET @col = @col+1
END
GO
SELECT * FROM TriggerTest

I have coded for an update in the trigger to print the mask as well as a message if the column was updated; in position eight, above, the mask value of type int is 128. Here is the output:

0x80
128
UPDATED

No problem here, the column is updated. Now run the script but 1) uncomment column nine above, 2) change the following so you update column 9 and see what the mask value is for that position:

IF COLUMNS_UPDATED() & 256= 256 
       PRINT 'UPDATED' 

DECLARE @col INT=1
WHILE @col <= 9
BEGIN
    DECLARE @str varchar (max) = 'UPDATE TriggerTest SET UpdateCol'+CAST(@col AS varchar (20))

+ '=999';
    IF @col=9
        EXEC (@str)    
    SET @col = @col+1
END
GO

Here are the results:

0x0001
1

So, the column is updated, but you don’t get the ‘UPDATED’ message because the value of the mask did not move by a power of 2. It changed.  Check out the attachment that I have included, where I printed the bit mask value for 8,9,10,20, and 30 column tables, and notice the value of the mask actually changes for each iteration.

Let’s look at one column – column 3 – for a table in the attachment.

You can see here that if you were wanting to pluck out whether or not column ordinal 3 was updated if, for a nine column table, you would have to mask 1024, but if you added 10 columns to the table you would have to mask 262144. Let’s do that one.  Uncomment the script above so that 20 columns are created, and run the following ALTER:

IF COLUMNS_UPDATED() & 262144= 262144
       PRINT 'UPDATED' 

DECLARE @col INT=1
WHILE @col <= 20
BEGIN
    DECLARE @str varchar (max) = 'UPDATE TriggerTest SET UpdateCol'+CAST(@col AS varchar (20))

+ '=999';
    IF @col=20
        EXEC (@str)    
    SET @col = @col+1
END
GO

0x040000
262144
UPDATED

So the issue is how Microsoft built the masking mechanism.  What they instructed you to do you can’t do with a table having more than eight columns.  The values 0x0400 is not the same value as 0x040000, which isn’t the same as 0x04000000.  I guess I would have counted from the right, and done 0x000000000004; the problem with this, of course is that you will eventually run out of space to mask columns, and I think these days they let you create 1024 columns in a table, and that would be a problem, because at column number 123 you would need to mask 5316911983139663500000000000000000000.0, and that’s the largest number of a decimal (38,0) in SQL Server, nowhere close to 1024 columns.  Given this info, it would be just as easy to skip COLUMNS_UPDATED() and just use the UPDATE().

Thanks,

Lee Everest

---------------------------

So I wonder how long takes for this to show up on Pinal Dave’s site