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.
» 
Comment and Contribute
