Bits in SQL Server

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.

Previous article
Next article

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles