6

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?

2

1 Answer 1

13

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

4
  • 2
    Awesome explanation with example. Aug 17, 2016 at 12:40
  • 4
    I love the (var)binary limitation. And by love, I mean I totally don't understand why such an esoteric limitation should exist, when we're patently intent on comparing binary values. Why you can't run SELECT 0x03 & 0x01 is very strange.
    – Hannah Vernon
    Aug 17, 2016 at 14:50
  • 2
    @MdHaidarAliKhan Thanks. The answer is even better now :) Aug 17, 2016 at 16:15
  • 2
    @MaxVernon Exactly. Maybe internally they just had it as a 64 bit INT and didn't want to change it? Also, good idea for that edit. I was in the middle of an edit when you made the change, so I incorporated the idea into my edit. Aug 17, 2016 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.