Bits in SQL Server

July 14, 2004

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers