Skip to content

SQL Server 2005 doesn’t like bitwise comparison of large numbers

An update trigger using COLUMNS_UPDATED() was failing, and I figured out the problem:

Some simple SQL:

SELECT CAST(0x0200 as bigint), 0x0200 | 0
SELECT CAST(0x02000 as bigint), 0x02000 | 0
SELECT CAST(0x020000 as bigint), 0x020000 | 0
SELECT CAST(0x0200000 as bigint), 0x0200000 | 0
SELECT CAST(0x02000000 as bigint), 0x02000000 | 0
SELECT CAST(0x020000000 as bigint), 0x020000000 | 0
SELECT CAST(0x0200000000 as bigint), 0x0200000000 | 0
I had “IF COLUMNS_UPDATED() | 0 > 0” in my trigger, and on large values of COLUMNS_UPDATED(), bitwise ORing with zero equals zero.  Of course, “IF COLUMNS_UPDATED() | 0 > 0” is a retarded way to say “IF COLUMNS_UPDATED() > 0”, but whatever codepath that large number triggers wigs out there to.  Try these out:

SELECT 1 WHERE 0x0200000000 > 0
SELECT 1 WHERE CAST(0x0200000000 as bigint) > 0

So, to fix my trigger, I have “IF CAST(COLUMNS_UPDATED() as bigint) > 0”, which still seems a little retarded, but at least my trigger works.