How to read/write full 32 or 64 bits of an int or bigint bitmasked field in TSQL.

Question

Setting the 32nd and 64th bits is tricky.

32-bit Solution:

I got it to work for 32-bit fields. The trick is to cast the return value of the POWER function to binary(4) before casting it to int. If you try to cast directly to int, without first casting to binary(4), you will get an arithmetic overflow exception when operating on the 32nd bit (index 31). Also, you must ensure the expression passed to POWER is of a sufficiently large type (e.g. bigint) to store the maximum return value (2^31), or the POWER function will throw an arithmetic overflow exception.

CREATE FUNCTION [dbo].[SetIntBit] 
(
    @bitfieldvalue int,
    @bitindex int, --(0 to 31)
    @bit bit --(0 or 1)
)
RETURNS int
AS
BEGIN
    DECLARE @bitmask int = CAST(CAST(POWER(CAST(2 as bigint),@bitindex) as binary(4)) as int);
    RETURN
    CASE
        WHEN @bit = 1 THEN (@bitfieldvalue | @bitmask)
        WHEN @bit = 0 THEN (@bitfieldvalue & ~@bitmask)
        ELSE @bitfieldvalue --NO CHANGE
    END
END

---

64-bit Problem:

I was going to use a similar approach for 64-bit fields, however I'm finding that the POWER function is returning inaccurate values, despite using the decimal(38) type for the expression/return value. For example: "select POWER(CAST(2 as decimal(38)), 64)" returns 18446744073709552000 (only the first 16 digits are accurate) rather than the correct value of 18446744073709551616. And even though I'd only raise 2 to the 63rd power, that result is still inaccurate.

The documentation of the POWER function indicates that "Internal conversion to float can cause loss of precision if either the money or numeric data types are used." (note that numeric type is functionally equivalent to decimal type).

I think the only way to handle 64-bit fields properly is to operate on their 32-bit halves, but that involves an extra check on the @bitindex property to see which half I need to operate on. Are there any built-in function or better ways to explicitly set those final bits in 32-bit and 64-bit bitmasked fields in TSQL?

Answer 1

64-bit Solution:

So far, the simplest solution I can come up with to my own question is to add an exceptional case for problematic computation of the bitmask for the 64th bit (i.e. 2^63), where the bitmask value is hardcoded so that it does not have to be computed by POWER. POWER computes 2^62 and smaller values accurately as far as I can see.

CREATE FUNCTION [dbo].[SetBigIntBit] 
(
    @bitfieldvalue bigint,
    @bitindex int, --(0 to 63)
    @bit bit --(0 or 1)
)
RETURNS bigint
AS
BEGIN
    DECLARE @bitmask bigint = case WHEN @bitindex = 63 THEN CAST(0x8000000000000000 as bigint)
    ELSE CAST(CAST(POWER(CAST(2 as bigint),@bitindex) as binary(8)) as bigint)
    RETURN
    CASE
        WHEN @bit = 1 THEN (@bitfieldvalue | @bitmask)
        WHEN @bit = 0 THEN (@bitfieldvalue & ~@bitmask)
        ELSE @bitfieldvalue --NO CHANGE
    END
END

EDIT: Here's some code to test the above function...

declare @bitfield bigint = 0;
print @bitfield;
declare @bitindex int;
set @bitindex = 0;
while @bitindex < 64
begin
  set @bitfield = tutor.dbo.SetBigIntBit(@bitfield,@bitindex,1);
  print @bitfield;  
  set @bitindex = @bitindex + 1;
end
set @bitindex = 0;
while @bitindex < 64
begin
  set @bitfield = tutor.dbo.SetBigIntBit(@bitfield,@bitindex,0);
  print @bitfield;  
  set @bitindex = @bitindex + 1;
end