October 27, 2016 at 1:33 pm
In the Update stored procedure created when you use replication there's a variable passed in as a binary that indicates if a field has changed. Each position of the binary can hold the value for 8 columns. When the update query checks to see if the field has changed, it does it with a SUBSTRING() on the binary variable.
DECLARE @bitmap binary(10), @c1 varchar(50), @c2 int
UPDATE MySchema.MyTable
SET
FirstName = CASE SUBSTRING(@bitmap,1,1) & 1 WHEN 1 THEN @c1 ELSE FirstName END
WHERE MyPK = @c2
What I don't understand is the & 1 part. I get that as you go through the columns you increment the first number in the substring every 8 columns, so the 9th column would be SUBSTRING(@bitmap,2,1) & 1 = 1
And I understand that the first 8 columns are
SUBSTRING(@bitmap,1,1) & 1 = 1
SUBSTRING(@bitmap,1,1) & 2 = 2
SUBSTRING(@bitmap,1,1) & 4 = 4
SUBSTRING(@bitmap,1,1) & 8 = 8
and so on, but I don't see how to populate this kind of construct manually. I also don't know why the & part is working the way it is. I tried this out and found that both
SUBSTRING(@bitmap,1,1) & 32 = 32 and
SUBSTRING(@bitmap,1,1) & 33 = 32 return true, but why? How is that 1,2,4,8,16,32,64 aspect set? How does this let the one position in the binary contain 8 values? How can it tell from what is passed in that column 1 changed, but column 2 didn't?
I've tried googling this, but have come up empty. If anyone could point me at a good explanation, I'd appreciate it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 27, 2016 at 2:17 pm
Hope this makes sense. I understand bitmaps, but I have difficulty in explaining them. (So do I really understand it)
If you have 2 lamps, which condition means that they are both on?
0 & 0 = 0 -- Lamp 1 Off and Lamp 2 Off = No
0 & 1 = 0 -- Lamp 1 Off and Lamp 2 On = No
1 & 0 = 0 -- Lamp 1 On and Lamp 2 Off = No
1 & 1 = 1 -- Lamp 1 On and Lamp 2 On = Yes
Considder an 8 bit binary bitmap
All bits off = 00000000 = 0
All bits on = 11111111 = 255 (2^0 + 2^1 + 2^2 + 2^3 + 2^4 + 2^5 + 2^6 + 2^7)
Lets look at 10 & 2
00001010 (10)
00000010 ( 2)
BINARY AND 00000010 = ( 2)
Now lets look at 11 & 9
00001011 (11)
00001001 ( 9)
BINARY AND 00001001 = ( 9)
Now lets look at 11 & 3
00001011 (11)
00000011 ( 3)
BINARY AND 00000011 = ( 3)
Now, lets look at 255 & 32
11111111
00100000
BINARY AND 00100000 = 32
Now, lets look at 255 & 33
11111111
00100001
BINARY AND 00100001 = 33
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply