Using Bits to Store Data
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/usingbitstostoredata.asp
-
Very cool! Extremely useful article about architecture to use bit operations!
In my case, I could use this to simulate bitmap indexes.
Regards,
Geert
-
I love bits... when you understand the bits, then all else falls into place.
Also makes me think back to the good ol' M/L days on the 6502, 8088, Z80
.Thanks David for writing about the bits.
In SQL Server there are 2 usefull bit functions supplied by MS in the Master DB and available in any current database.
( I do not like their "all lowercase" names
)Select fn_replinttobitstring(65535) -- 32 bits of passed integer
and
select fn_replbitstringtoint('0000000000000011111111111111111') -- integer of passed bit string
Once you understand the BITs, all the pieces come together -
What??
Now THIS is good news, I'm building an ETL process here in which I'll process flags with the bitmap technique described in the article.
Damn, I'd be building these functions myself, I have never found any docs about these, not even in Microsoft Press books.
Thanks for mentioning the functions, ThomasH !
Regards,
Geert
-
Pause a moment whilst I swear profusely
I wish I had known about fn_replinttobitstring & fn_replbitstringtoint, it would have made my life so much easier!
I'll add them to my little black book of undocumented functions!
Thanks
-
I wrote a function that expands to 64 bits (8x8) that might be usefull...
Create Function fn_BigIntToBinary (@bi BigInt)
RETURNS Varchar(72)
AS
Begin
/*
-- Returns 64 bits of passed BigInt, each byte separated by a space.
Select dbo.fn_BigIntToBinary(123456789)
*/
Declare @RetVal Varchar(72), @Hi16a BigInt, @Hi16b Bigint, @Low16a BigInt, @Low16b BigInt,
@Hi32 BigInt, @RetVal1 VarChar(72)
Select @Hi32 = @bi / Power(Convert(BigInt,2), 32)
select @Hi16a = (@bi / (65536 * 256)) & (Power(2, 16) - 1),
@Hi16b = (@bi / 65536) & (Power(2, 16) - 1),
@Low16a = (@bi / 256) & (Power(2, 16) - 1),
@Low16b = @bi & (Power(2, 16) - 1),
@RetVal1 = case
When @bi > Power(Convert(BigInt,2), 32) then dbo.fn_BigIntToBinary(@Hi32)
Else '00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000'
End
Set @RetVal = Right(@RetVal1, 35) + ' ' +
Right(fn_replinttobitstring(@Hi16a), 8) + ' ' +
Right(fn_replinttobitstring(@Hi16b), 8) + ' ' +
Right(fn_replinttobitstring(@Low16a), 8) + ' ' +
Right(fn_replinttobitstring(@Low16b), 8)
Return @RetVal
End
Hope it cut&pastes ok....
Once you understand the BITs, all the pieces come together -
If you know your column is a bitmap-column,how do you decipher it ?
e.g. msdb.dbo.sysjobschedule-table column freq_interval back to Su/Mo/Tu/We/Th/Fr/Sa
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
RTFM.
Use test conditions described in article "Using bits to Store Data".
-
Another approach:
Let's check for Friday, which is bit 7, I presume (I have no clue, check out in the Books online)
Bitmap is: 0000 0010
Integer value is: 2
AND the bitmap field with this integer value
Field & 2
Result is 2
If field is NOT friday, result will be zero.
Thus:
CASE WHEN Field|1=1 THEN 'Saturday'
WHEN field|2=2 THEN 'Friday'
....
END
But still I recommend you to read the article, because a bitmap may contain different "1" bits. The CASE statement will see the first one and ignore the rest.
-
Ok it took a while , but I finaly got the picture.
case when convert(binary(2),freq_interval ) & 1 = 1 then 'Su-'
else '..-' end
+ case when convert(binary(2),freq_interval ) & 2 = 2 then 'Mo-'
else '..-' end
+ case when convert(binary(2),freq_interval ) & 4 = 4 then 'Tu-'
else '..-' end
+ case when convert(binary(2),freq_interval ) & 8 = 8 then 'We-'
else '..-' end
+ case when convert(binary(2),freq_interval ) & 16 = 16 then 'Th-'
else '..-' end
+ case when convert(binary(2),freq_interval ) & 32 = 32 then 'Fr-'
else '..-' end
+ case when convert(binary(2),freq_interval ) & 64 = 64 then 'Sa'
else '..' end
Thank you for your patience
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Exactly
When you want to know if it's on Monday AND Thursday, just add their values together and AND the result
WHEN freq_interval | (2+16) = (2+16) THEN 'Monday AND Thursday'
You can use 18, but this is not so readible.
-
I have a small issue
. I have jobs with workphases and need a bitwise OR() aggregate function for the Status field.Example: Select JobID, OR(Status)
From Jobs join Workphases on Jobs.JobID = Workphases.JobID
Sum doesn't work.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply