SQL Server – How Does the & Operator Work

bitwise-comparisonoperatorsql serversql server 2014t-sql

I was running a trace on one of our test servers when someone did this:

enter image description here

and one of the queries I could catch in the trace was:

declare @UserOption int
select @UserOption=convert(int, c.value) from sys.configurations c where c.name='user options'

SELECT
CAST(@UserOption & 1 AS bit) AS [DisableDefaultConstraintCheck],
CAST(@UserOption & 2 AS bit) AS [ImplicitTransactions],
CAST(@UserOption & 4 AS bit) AS [CursorCloseOnCommit],
CAST(@UserOption & 8 AS bit) AS [AnsiWarnings],
CAST(@UserOption & 16 AS bit) AS [AnsiPadding],
CAST(@UserOption & 32 AS bit) AS [AnsiNulls],
CAST(@UserOption & 64 AS bit) AS [AbortOnArithmeticErrors],
CAST(@UserOption & 128 AS bit) AS [IgnoreArithmeticErrors],
CAST(@UserOption & 256 AS bit) AS [QuotedIdentifier],
CAST(@UserOption & 512 AS bit) AS [NoCount],
CAST(@UserOption & 1024 AS bit) AS [AnsiNullDefaultOn],
CAST(@UserOption & 2048 AS bit) AS [AnsiNullDefaultOff],
CAST(@UserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull],
CAST(@UserOption & 8192 AS bit) AS [NumericRoundAbort],
CAST(@UserOption & 16384 AS bit) AS [AbortTransactionOnError]

there is lots of information that can fit in a int if you know –

How does the operator & work?

Best Answer

A single ampersand is the Bitwise AND operator. The numbers on either side represent the value of one or more bit positions:

Actual binary (not BINARY datatype), evaluated from right to left -> 00100010

BIT    Position    Value (Position ^ 2)         "Selected"
0       0                            1                 0
1       1                            2                 2
0       2                            4                 0
0       3                            8                 0
0       4                           16                 0
1       5                           32                32
0       6                           64                 0
0       7                          128                 0
...   ...                          ...               ...
0      62          4611686018427387904                 0

Hence, 34 is made up of bitwise values of 2 and 32.

A very simple example showing that the & operator will return whatever value(s) is/are present on both sides is:

SELECT 1 & 1 -- 1
SELECT 1 & 2 -- 0 (because 2 is made up of only 2)
SELECT 1 & 3 -- 1 (because 3 is made up of 1 and 2)

SELECT 2 & 2 -- 2
SELECT 2 & 3 -- 2 (because 3 is made up of 1 and 2)
SELECT 2 & 4 -- 0 (because 4 is made up of only 4)

SELECT 3 &  4 -- 0 (because 3 is made up of 1 and 2, while 4 is made up of only 4)
SELECT 3 &  5 -- 1 (because 3 is made up of 1 and 2, while 5 is made up of 1 and 4)
SELECT 3 & 11 -- 3 (because 3 is made up of 1 and 2, while 11 is made up of 1, 2, and 8)

Looking back at the code you posted in the question, you should be able to see how it can be used to determine if one or more bits / flags are "set" within a given value. For example, let's say you want to require that AnsiPadding (value = 16), AnsiNulls (value = 32) and QuotedIdentifier (value = 256) are set to ON. You would add up those individual values -- 16 + 32 + 256 -- and then compare the total -- 304 -- with some "current" value, let's say 7000 (values 8 + 16 + 64 + 256 + 512 + 2048 + 4096):

SELECT 304 & 7000; -- 272

The result, 272, is the combination of 16 and 256. So if you want to require those 3 options (additional options will not adversely affect the result), you would use:

IF (@options & 304 = 304)
BEGIN
  ...
END;

As you can see, bitwise operations allow you to extract one or more "options" from one or more "options". But, in the code that you posted, they are just looking for individual options since using & with an individual bitwise value will return 0 if not present or that particular bitwise value if present. And the CAST to the BIT datatype will reduce all of those variations to either 0 (if the & yielded a 0) or 1 (if the & yielded anything else).

Please note that for the bitwise operators, either side (i.e. expression) can be:

any of the data types of the integer data type category, or the bit, or the binary or varbinary data types.

However, counter-intuitively:

In a bitwise operation, only one expression can be of either binary or varbinary data type.

That's right: "binary" operations do not work on purely "binary" data ;-). Attempting to do what seems entirely reasonable:

SELECT 0x03 & 0x0B;

will result in the following error:

Msg 402, Level 16, State 1, Line 28
The data types varbinary and varbinary are incompatible in the '&' operator.

In practical terms, this means that bitwise operations are constrained by the upper-limit of the BIGINT datatype (which is 8 bytes / 64 bits). Hence:

  • Total bits (i.e. flags / options): 63 (the 64th bit is used for negatives as BIGINT is signed)
  • Largest individual bit value: 4611686018427387904 (i.e. 2 ^ 62 ; 62 being the 63rd bit position))
  • Largest usable value: 9223372036854775807 (i.e. (2 ^ 63) - 1 ; all 63 bits present / set to "on")

Meaning: if you need to handle more than 63 flags / options, then you won't be using the & operator ;-).

Related Question