Update one certain bit in a bitmask field (SQL Server)

Question

I have an int column containing 8 bits in my SQL Server database. How can I update certain bit without affecting others?

For example, I have a value

11010000 

and I want to set bit1 and bit2 to 1, so it would become

11010011

Looked through bitwise operators but couldn't find proper solution.

My goal is not only to update certain bit, but also to avoid database locks.

So when transaction1 updates bit1 in a certain record, another transaction2 could update bit2 in the same field of the same record at the same time.

Is this possible? Or is using 8 separate bit columns the only way?

Answer 1

A pseudo-bitset value is when you have an integer or string that, when selected or printed, looks like a bitset. Something like this:

DECLARE @bits varchar(8) = '1010'

SELECT Right(8, REPLICATIE('0', 8) + @bits

Value returned: 00001010

For this type of bitset, you need to remember that you are not setting the bits, but are representing the bits instead, and you need to set the representation of the bits in the same way. For example, the function below can be used to set bits in a string representation of your bitstring:

CREATE FUNCTION dbo.SetBitInString 
    (
    @Source char(8), 
    @Position int,
    @Action char(1) = 'S',
    @Value bit = NULL
    )
RETURNS Char(8)
AS
BEGIN
    DECLARE @work Char(8) = '00000000'
    SET @work = Right(@work + ISNULL(LTRIM(RTRIM(@Source)), ''), 8)
    SET @Work = 
        CASE @Action
            WHEN 'S'-- Set
                THEN Stuff(@Work, @Position, 1, '1')
            WHEN 'R' -- Reset
                THEN Stuff(@Work, @Position, 1, '0')
            WHEN 'X' -- XOR value with position
                THEN STUFF(@Work, @Position, 1, CAST(CAST(SubString(@Work, @Position, 1) as int) ^ ISNULL(@Value, 0) as CHAR(1)))
            WHEN 'Q'
                THEN '1'
            ---- add other options as needed - this is a quick example.
            ELSE @Action
        END
    IF (@Action = @Work)
        RAISERROR('Bad Action (%s) Submitted', 13, 1, @Action)

    RETURN @Work
END

Reading a bit is a simple SUBSTRING. Constants can be defined for the meaning of each bit (e.g. DECLARE @ShowTotalPrice = 4 -- The 4th bit is the Show Total Price flag)

This should give you enough to go on if you want to use this style of setting, where the displayed value is your bitset represented in 1s and 0s.