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

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....

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

     Jul 13  

Sometimes 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

     Jul 13  

Sometimes this is me