Bits in SQL Server

Wednesday Jul 14th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

Learn how to analyze and understand how SQL Server uses Binary digits for categorizing and how to query such categorized binary numbers to produce detailed results.

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.

By creating a function similar to the one below, you are able to query such binary positions. This function displays all of the user options that are enabled.

Create function dbo.UDF_Displayuseroptions 
	(@number smallint)
returns varchar(3300)
as
begin
declare @binary varchar(3300)
select @binary = 
CASE @number&16384 WHEN 16384 
  THEN 'XACT_ABORT ' +char(13)+char(10) 
  ELSE '' END+
CASE @number&8192 WHEN 8192 
  THEN ' NUMERIC_ROUNDABORT ' 
    +char(13)+char(10) 
  ELSE '' END+
CASE @number&4096 WHEN 4096 
  THEN 'CONCAT_NULL_YIELDS_NULL' 
    +char(13)+char(10) 
  ELSE '' END+
CASE @number&2048 WHEN 2048 
  THEN 'ANSI_NULL_DFLT_OFF' 
    +char(13)+char(10) 
  ELSE '' END+
CASE @number&1024 WHEN 1024 
  THEN 'ANSI_NULL_DFLT_ON' 
    +char(13)+char(10) 
  ELSE '' END+
CASE @number&512 WHEN 512 
  THEN 'NOCOUNT' +char(13)+char(10) 
  ELSE '' END+
CASE @number&256 WHEN 256 
  THEN 'QUOTED_IDENTIFIER' 
    +char(13)+char(10) 
  ELSE '' END+
CASE @number&128 WHEN 128 
  THEN 'ARITHIGNORE' +char(13)+char(10) 
  ELSE '' END+
CASE @number&64 WHEN 64 
  THEN 'ARITHABORT' +char(13)+char(10) 
  ELSE '' END+
CASE @number&32 WHEN 32 
  THEN 'ANSI_NULLS' +char(13)+char(10) 
  ELSE '' END+
CASE @number&16 WHEN 16 
  THEN 'ANSI_PADDING' +char(13)+char(10) 
  ELSE '' END+
CASE @number&8 WHEN 8 
  THEN 'ANSI_WARNINGS' +char(13)+char(10) 
  ELSE '' END+
CASE @number&4 WHEN 4 
  THEN 'CURSOR_CLOSE_ON_COMMIT' +char(13)+char(10) 
  ELSE '' END+
CASE @number&2 WHEN 2 
  THEN 'IMPLICIT_TRANSACTIONS' +char(13)+char(10) 
  ELSE '' END
+CASE @number&1 WHEN 1 
  THEN 'DISABLE_DEF_CNST_CHK' +char(13)+char(10) 
  ELSE '' END
return @binary
end

Query

Select @@options

Results

6008

Query

Select dbo.UDF_Displayuseroptions (@@options)

Results

CONCAT_NULL_YIELDS_NULL
ANSI_NULL_DFLT_ON
NOCOUNT
QUOTED_IDENTIFIER
ARITHABORT
ANSI_NULLS
ANSI

Query

Let's disable the "nocount" option in the user option by executing the below query:

Set nocount off

Query

Let us query the user option now.

Select @@options

Results

5496

Query

Let us query the user options by using the function we created.

Select dbo.UDF_Displayuseroptions (@@options)

Results

CONCAT_NULL_YIELDS_NULL
ANSI_NULL_DFLT_ON
QUOTED_IDENTIFIER
ARITHABORT
ANSI_NULLS
ANSI_PADDING

You can also create a function like the one below to see what options are enabled and disabled.

Create  function dbo.UDF_DisplayAlluseroptions (@number smallint)
returns varchar(3300)
as
begin
declare @binary varchar(3300)
select @binary = 
CASE @number&16384 WHEN 16384 THEN 'XACT_ABORT ON ' +char(13)+char(10) 
	ELSE 'XACT_ABORT OFF ' +char(13)+char(10)  END+
CASE @number&8192 WHEN 8192 THEN ' NUMERIC_ROUNDABORT ON ' +char(13)+char(10) 
	ELSE 'NUMERIC_ROUNDABORT OFF ' +char(13)+char(10)  END+
CASE @number&4096 WHEN 4096 THEN 'CONCAT_NULL_YIELDS_NULL ON ' +char(13)+char(10) 
	ELSE 'CONCAT_NULL_YIELDS_NULL OFF ' +char(13)+char(10)  END+
CASE @number&2048 WHEN 2048 THEN 'ANSI_NULL_DFLT_OFF' +char(13)+char(10) ELSE '' END+
CASE @number&1024 WHEN 1024 THEN 'ANSI_NULL_DFLT_ON' +char(13)+char(10) ELSE '' END+
CASE @number&512 WHEN 512 THEN 'NOCOUNT ON ' +char(13)+char(10) 
	ELSE 'NOCOUNT OFF ' +char(13)+char(10)  END+
CASE @number&256 WHEN 256 THEN 'QUOTED_IDENTIFIER ON ' +char(13)+char(10) 
	ELSE 'QUOTED_IDENTIFIER OFF ' +char(13)+char(10)  END+
CASE @number&128 WHEN 128 THEN 'ARITHIGNORE ON ' +char(13)+char(10) 
	ELSE 'ARITHIGNORE OFF ' +char(13)+char(10)  END+
CASE @number&64 WHEN 64 THEN 'ARITHABORT ON ' +char(13)+char(10) 
	ELSE 'ARITHABORT OFF ' +char(13)+char(10)  END+
CASE @number&32 WHEN 32 THEN 'ANSI_NULLS ON ' +char(13)+char(10) 
	ELSE 'ANSI_NULLS OFF ' +char(13)+char(10)  END+
CASE @number&16 WHEN 16 THEN 'ANSI_PADDING ON ' +char(13)+char(10) 
	ELSE 'ANSI_PADDING OFF ' +char(13)+char(10)  END+
CASE @number&8 WHEN 8 THEN 'ANSI_WARNINGS ON ' +char(13)+char(10) 
	ELSE 'ANSI_WARNINGS OFF ' +char(13)+char(10)  END+
CASE @number&4 WHEN 4 THEN 'CURSOR_CLOSE_ON_COMMIT ON ' +char(13)+char(10) 
	ELSE 'CURSOR_CLOSE_ON_COMMIT OFF ' +char(13)+char(10)  END+
CASE @number&2 WHEN 2 THEN 'IMPLICIT_TRANSACTIONS ON ' +char(13)+char(10) 
	ELSE 'IMPLICIT_TRANSACTIONS OFF '+char(13)+char(10)  END+
CASE @number&1 WHEN 1 THEN 'DISABLE_DEF_CNST_CHK ON ' +char(13)+char(10) 
	ELSE 'DISABLE_DEF_CNST_CHK OFF '+char(13)+char(10)  END
return @binary
end

Query

Select @@options

Results

5496

Query

Select dbo.UDF_DisplayAlluseroptions(@@options)

Results

XACT_ABORT OFF 
NUMERIC_ROUNDABORT OFF 
CONCAT_NULL_YIELDS_NULL ON 
ANSI_NULL_DFLT_ON
NOCOUNT OFF 
QUOTED_IDENTIFIER ON 
ARITHIGNORE OFF 
ARITHABORT ON 
ANSI_NULLS ON 
ANSI_PADDING ON 
ANSI_WARNINGS ON 
CURSOR_CLOSE_ON_COMMIT OFF 
IMPLICIT_TRANSACTIONS OFF 
DISABLE_DEF_CNST_CHK OFF 

Another Example on how SQL Server uses binary positions is the table sysjobschedules in the database MSDB. Whenever we create any schedules, it is imperative to configure a job to run on certain days. Lets take the below example. Create a job schedule x and configure it to run on Sunday, Monday, Wednesday, Thursday and Friday (as shown in figure 1.0).


Figure 1.0

When we query the sysjobschedules table in MSDB database, we get result 59.

Query

select freq_interval  from sysjobschedules where name='x'

Results

59

The table sysjobschedules in MSDB database stores the freq_interval in the following manner.

1 = Sunday

2 = Monday

4 = Tuesday

8 = Wednesday

16 = Thursday

32 = Friday

64 = Saturday

In order to find what 59 means, we can write the query below or create a function using this query.

Query

select Frequency=
CASE freq_interval  &64 WHEN 64 THEN 'Saturday'+char(13)+char(10) ELSE '' END+
CASE freq_interval  &32 WHEN 32 THEN 'Friday'+char(13)+char(10) ELSE '' END+
CASE freq_interval  &16 WHEN 16 THEN 'Thursday'+char(13)+char(10) ELSE '' END+
CASE freq_interval  &8 WHEN 8 THEN 'Wednesday'+char(13)+char(10) ELSE '' END+
CASE freq_interval  &4 WHEN 4 THEN 'Tuesday'+char(13)+char(10) ELSE '' END+
CASE freq_interval  &2 WHEN 2 THEN 'Monday'+char(13)+char(10) ELSE '' END
+CASE freq_interval  &1 WHEN 1 THEN 'Sunday'+char(13)+char(10) ELSE '' END
from sysjobschedules where name ='x'

Results

Frequency

Friday
Thursday
Wednesday
Monday
Sunday

Conclusion

The main intent of this article is to analyze and understand how SQL Server uses Binary digits for categorizing and to query such categorized binary numbers to produce detailed results. SQL Server uses binary digits for categorizing in many system tables such as sysdatabases, sysjobschedules, sysdevices, etc. You can use the above method to query such binary information.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved