• & (bitwise AND)
• | (bitwise OR)
• ~ (bitwise NOT)
• ^ (bitwise XOR)

I'm not going to get into what is the syntax of each of these operators, since you can see that in SQL Server Books Online. What we will see is how to use the bitwise AND operator to determine the various bits that are turned on in a given number. A given integer number has 4 bytes of storage and supports a maximum value of 2147483647. If you are familiar with bit arithmetic , the method that you use to determine the various bit positions is 2^n (2 to the power n), where n ranges from 0, 1, 2, 3, 4 ... For example, each bit position will be 2^0, 2^1, 2^2 etc. The AND operator works as specificed in the following table:

AND	0	1
0	0	0
1	0	1

The logic is very simple, if both bits turned on, AND returns 1, otherwise for all cases it returns 0. Now, if you want to check the bits of a number to determine if it is turned on, we form the appropriate bit-mask (2^n value) and then AND that number with the original number. If the result is 2^n, then the bit identified by 2^n is turned on. For example, if you take the number 4, bit number 3 is turned on. If you AND 4 with 2^2 and the result is 2^2, then bit number 3 is turned on (since bit numbers start from 0).

Here is the T-SQL block that shows this logic.

DECLARE @originalNumber INT
DECLARE @bitMask INT
DECLARE @counter INT
BEGIN
	SET @bitMask = 1
	SET @originalNumber = 7
	SET @counter = 1

	WHILE (@counter < 31)
	BEGIN
		IF ((@originalNumber & @bitMask) = @bitMask)
		BEGIN
			PRINT 'Bit Number (' + LTRIM(RTRIM(STR(@counter))) + ') is ON'
		END
		SET @bitMask = @bitMask * 2
		SET @counter = @counter + 1
	END
	-- Hits here when the 31st bit needs to be checked. We do it here
	-- since if we multiply by 2, the number we get is 2147483648, which 
	-- results in an overflow
	SET @bitMask = 2147483647
	IF ((@originalNumber & @bitMask) = @bitMask)
	BEGIN
		PRINT 'Bit Number (' + LTRIM(RTRIM(STR(@counter))) + ') is ON'
	END
END