The
objective of this article is to analyze and understand how SQL Server uses
Binary digits for categorizing and how to query such categorized binary numbers
to produce detailed results.
What is Bit?
Bit is the
short form for binary digit.
What is Binary?
Binary is
a base 2 numbering system. A binary notation consists of two characters: 0 and 1.
This is
the way to count up to 128 in binary
| Decimal-Binary Conversion | ||
|---|---|---|
| Decimal | Binary | Place Value |
| 1 | 1 | 20 |
| 2 | 10 | 21 |
| 4 | 100 | 22 |
| 8 | 1000 | 23 |
| 16 | 10000 | 24 |
| 32 | 100000 | 25 |
| 64 | 1000000 | 26 |
| 128 | 10000000 | 27 |
Usage of Bit positions in SQL Server
SQL Server
uses binary digit positions to categorize different choices in many places,
especially in system tables and some global variables.
For example,
when you execute the query "Select @@Options" SQL Server outputs a number
that has a definite meaning. The table below shows how SQL Server categorizes
user options using binary digit positions. Multiple bits can be on at the same
time.
| Value | Configuration | Description |
|---|---|---|
| 1 | DISABLE_DEF_CNST_CHK | Controls interim or deferred constraint checking. |
| 2 | IMPLICIT_TRANSACTIONS | Controls whether a transaction is started implicitly when a statement is executed. |
| 4 | CURSOR_CLOSE_ON_COMMIT | Controls behavior of cursors after a commit operation has been performed. |
| 8 | ANSI_WARNINGS | Controls truncation and NULL in aggregate warnings. |
| 16 | ANSI_PADDING | Controls padding of fixed-length variables. |
| 32 | ANSI_NULLS | Controls NULL handling when using equality operators. |
| 64 | ARITHABORT | Terminates a query when an overflow or divide-by-zero error occurs during query execution. |
| 128 | ARITHIGNORE | Returns NULL when an overflow or divide-by-zero error occurs during a query. |
| 256 | QUOTED_IDENTIFIER | Differentiates between single and double quotation marks when evaluating an expression. |
| 512 | NOCOUNT | Turns off the message returned at the end of each statement that states how many rows were affected. |
| 1024 | ANSI_NULL_DFLT_ON | Alters the session’s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls. |
| 2048 | ANSI_NULL_DFLT_OFF | Alters the session’s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined not to allow nulls. |
| 4096 | CONCAT_NULL_YIELDS_NULL | Returns NULL when concatenating a NULL value with a string. |
| 8192 | NUMERIC_ROUNDABORT | Generates an error when a loss of precision occurs in an expression. |
| 16384 | XACT_ABORT | Rolls back a transaction if a Transact- SQL statement raises a run-time error. |
If you get
a result that says 5496, then it means it is the combination of
4096+1024+256+64+32.