September 7, 2010 at 1:53 pm
I have some significant strangeness going on with columns_updated() in an update trigger. It appears they inverted the bytes in the bitmasking, making it difficult to locate fields.
Setup:
/*
DROP TRIGGER TR_UpdTestTrig
GO
DROP TABLE UpdateTest
GO
*/
CREATE TABLE UpdateTest
(ColIDINTIDENTITY(1,1),
ColAVARCHAR(10),
ColBVARCHAR(10),
ColCVARCHAR(10),
ColDVARCHAR(10),
ColEVARCHAR(10),
ColFVARCHAR(10),
ColGVARCHAR(10),
ColHVARCHAR(10),
ColIVARCHAR(10),
ColJVARCHAR(10),
ColKVARCHAR(10),
ColLVARCHAR(10)
)
GO
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
GO
Here's where things go odd. According to Books online for SQL 2k5:
From: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/765fde44-1f95-4015-80a4-45388f18a42c.htm
In SQL Server 2005, 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 when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example:
It recommends running this (which I did):
SELECT TABLE_NAME, COLUMN_NAME,
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'UpdateTest';
GO
Now, theoretically, the results here SHOULD match the bitmapping from the columns, right? Well, next, drop in this trigger:
CREATE TRIGGER TR_UpdTestTrig ON UpdateTest FOR UPDATE AS
PRINT CAST( columns_updated() AS INT)
print columns_updated() & 1
GO
It does nothing except report what the columns_updated() is reporting. The second one in there is for confirmation that there wasn't anything funky going on with converting the varbinary to int for some reason, which confirms with one of the updates below.
When you run this, it says ColA's Column_ID is '2', and ColB is '3'. Theoretically, bitmapped to 2 and 4 respectively. ColH is Column_ID: 9
UPDATE UpdateTest
SETColA = 'xx'
where ColID = 1
UPDATE UpdateTest
SETColB = 'yy'
where ColID = 2
UPDATE UpdateTest
SETColC = 'zz'
where ColID = 3
UPDATE UpdateTest
SETColE = 'qq'
wherecolID = 4
UPDATE UpdateTest
SET ColH = 'pp'
where colID = 5
The results from setting colA/B/C/E/H are: 512/1024/2048/8192/1. Note, you count 8 DOWN to find the start of the previous bit, you don't even reverse count. Field #9 is the first item. I've also done a test expanding to three bytes worth of fields, and the pattern persists. (Fields 1-8 are in byte 3, 9-16 in byte 2, 17 alone in byte 1).
So, the pattern is there, but there is no way to easily locate this, books online is wrong about the location method, and it's incredibly counter-intuitive.
Did I miss a memo somewhere? Is there an intuitive equivalent of columns_updated that takes the field names? Do I need to mess with the mechanic somehow? The structure works just fine for 8 fields, or less.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 7, 2010 at 3:36 pm
Data this type of thing is stored as the Least Significant Byte First (on the "left"). That's why it appears to be backwords. Then, within each byte, the least significant BIT is on the "right".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2010 at 3:47 pm
Jeff Moden (9/7/2010)
Data this type of thing is stored as the Least Significant Byte First (on the "left"). That's why it appears to be backwards. Then, within each byte, the least significant BIT is on the "right".
Sorry Jeff, but that appears inaccurate, or I'm misunderstanding what you're saying. Least Significant byte is on right, from all appearances, at least via the following test:
DECLARE @blah AS VARBINARY(4)
SET @Blah = CAST( 260 AS VARBINARY(4))
--print cast( @blah AS INT)
--print @blah & 4
--print @Blah & 256
PRINT substring(@blah, 1, 1) & 4
PRINT substring(@blah, 2, 1) & 4
PRINT substring(@blah, 3, 1) & 4
PRINT substring(@blah, 4, 1) & 4
PRINT substring(@blah, 1, 1) & 1
PRINT substring(@blah, 2, 1) & 1
PRINT substring(@blah, 3, 1) & 1
PRINT substring(@blah, 4, 1) & 1
You'll find the (4) in byte 4 and the (256) in the 1 spot in byte 3.
However, while your reply is appreciated, it doesn't really answer the resultant question(s).
Either SQL Books is wrong or I'm missing a hotfix of somekind that allows it to be right. I'm patched to SQL Server 9.00.3042.00 (SP2 with SSIS).
Failing that, since BOL is wrong (Amazing! I know!), I need a more intuitive way of dealing with the bitmasking for dealing with column update locations. At the moment we're using an equality where clause check to determine changes between inserted and deleted which, while functional, is slower.
While I could create code to deal with byte level confusion, the average junior/midlevel dba I would hand that off to might have an anneurism if it exploded at 3 in the morning. Please note, I'm a consultant. When they decide to change this code I've probably moved on through 2-3 jobs by then. I have to be careful about the intricate things I leave behind.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 7, 2010 at 3:57 pm
At the moment we're using an equality where clause check to determine changes between inserted and deleted which, while functional, is slower.
i think that's the only way to do it;
remember the UPDATE() function and the COLUMNS_UPDATED() function just returns whether the column was actually referenced in the insert/update statement, NOT whether the column changed it's value;
--cols a thru L are in the update() functions
INSERT INTO UpdateTest (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI, ColJ, ColK, ColL)
VALUES ('aa','bb','cc','dd','aa','bb','cc','dd','aa','bb','cc','dd')
--only cola and colb is in the UPDATE, both return true, but only cola changed it's value.
UPDATE UpdateTest set cola = 'aaa',colb = 'bb' WHERE cola = 'aa'
Lowell
September 7, 2010 at 4:09 pm
Lowell (9/7/2010)
remember the UPDATE() function and the COLUMNS_UPDATED() function just returns whether the column was actually referenced in the insert/update statement, NOT whether the column changed it's value;
If I'd known that I'd forgotten it. You're absolutely correct after I tested your example myself. Thanks Lowell. We're using a single stored proc to deal with this and if the param's null it isnull()'s to itself, so they'll always have an 'update'. That kills the entire adventure in general.
Well, my questions are answered, but I'm more then happy to continue discussing why Microsoft decided to use reverse byte association and not detect a way to use it with their built in functions. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 7, 2010 at 4:14 pm
I always felt those two functions should be renamed, as they are really misleading; something like ISREFERENCED would be a much better name .
Lowell
September 7, 2010 at 4:15 pm
I always felt those two functions should be renamed, as they are really misleading; something like ISREFERENCED would be a much better name
Lowell
September 8, 2010 at 4:51 am
Craig Farrell (9/7/2010)
or I'm misunderstanding what you're saying.
That may be true... from BOL 2k (had a handy reference there)...
F. Use COLUMNS_UPDATED to test more than 8 columns
If you must test for updates that affect columns other than the first 8 columns in a table, you must use the SUBSTRING function to test the proper bit returned by COLUMNS_UPDATED. This example tests for updates that affect columns 3, 5, or 9 in the Northwind.dbo.Customers table.
USE NorthwindDROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO
UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
Like I said, "Least significant byte is first". And it's not a Microsoft anomoly... that's the way data is stored for numbers at the machine language level.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2010 at 10:19 am
Jeff Moden (9/8/2010)
That may be true... from BOL 2k (had a handy reference there)...IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO
Like I said, "Least significant byte is first". And it's not a Microsoft anomoly... that's the way data is stored for numbers at the machine language level.
I would agree, if my test code above didn't show that least significant byte is last in a varbinary. BoL is inaccurate here, unfortunately, at least for 2k5.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2010 at 12:27 pm
Your test code is working with Varbinary... not with Columns_Updated.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2010 at 12:37 pm
Please note the sample build code in the original post, where it shows that the first set of columns (for more then 8 fields) are in the most significant byte. If you have 10 columns, columns 1-8 are in the first byte (agreed that substring(1,1) works for this), but that byte for hard value is the 256-32768 bit set. The second byte, which works for columns 9-16, is the 1-128 bit set.
The varbinary acts the same way, I just used it in the second set of code to help simplify the example.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2010 at 9:51 pm
Craig Farrell (9/8/2010)
Please note the sample build code in the original post, where it shows that the first set of columns (for more then 8 fields) are in the most significant byte. If you have 10 columns, columns 1-8 are in the first byte (agreed that substring(1,1) works for this), but that byte for hard value is the 256-32768 bit set. The second byte, which works for columns 9-16, is the 1-128 bit set.The varbinary acts the same way, I just used it in the second set of code to help simplify the example.
Heh... whatever. My point is that BOL isn't wrong for the item I posted. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2010 at 10:33 am
Jeff Moden (9/8/2010)
Heh... whatever. My point is that BOL isn't wrong for the item I posted. 🙂
For the item you posted, no, but since we're discussing 2k5 and that BOL is wrong for 2k5 because of the new method to locating the bitmapping, as described in the article from which I referenced earler (which is from 2k5) and then proved with sample code inaccurate, I guess this is appropriate:
Heh... whatever. 🙂
I know it exists now, I'll deal with it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 9, 2010 at 1:03 pm
Craig Farrell (9/9/2010)
Jeff Moden (9/8/2010)
Heh... whatever. My point is that BOL isn't wrong for the item I posted. 🙂For the item you posted, no, but since we're discussing 2k5 and that BOL is wrong for 2k5 because of the new method to locating the bitmapping, as described in the article from which I referenced earler (which is from 2k5) and then proved with sample code inaccurate, I guess this is appropriate:
Heh... whatever. 🙂
I know it exists now, I'll deal with it.
Ah sorry... my apologies. Not sure why I was hung up on the 2k thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply