Using “&” (Bitwise AND) operator in SQL Server

A quick example on using the “&” (Bitwise AND) operator in SQL Server.  This can be used to interpret status bits from an application. In this example I will use the status column from master..sysdatabases because it is easily available on SQL server so you don’t need to create any tables to test this example.

I’ve used 2 methods, but surely there will exist others…

First one uses a CASE statement, and is easy to read and understand:

SELECT 
 name,
 status, 
 CASE WHEN (status & 1 ) <> 0 THEN 1 ELSE 0 END as [autoclose],
 CASE WHEN (status & 4 ) <> 0 THEN 1 ELSE 0 END as [select into/bulkcopy] ,
 CASE WHEN (status & 8 ) <> 0 THEN 1 ELSE 0 END as [trunc. log on chkpt] ,
 CASE WHEN (status & 16 ) <> 0 THEN 1 ELSE 0 END as [torn page detection] ,
 CASE WHEN (status & 32 ) <> 0 THEN 1 ELSE 0 END as [loading] ,
 CASE WHEN (status & 64 ) <> 0 THEN 1 ELSE 0 END as [pre recovery] ,
 CASE WHEN (status & 128 ) <> 0 THEN 1 ELSE 0 END as [recovering] , 
 CASE WHEN (status & 256 ) <> 0 THEN 1 ELSE 0 END as [not recovered] ,
 CASE WHEN (status & 512 ) <> 0 THEN 1 ELSE 0 END as [offline] ,
 CASE WHEN (status & 1024 ) <> 0 THEN 1 ELSE 0 END as [read only] ,
 CASE WHEN (status & 2048 ) <> 0 THEN 1 ELSE 0 END as [dbo use only] ,
 CASE WHEN (status & 4096 ) <> 0 THEN 1 ELSE 0 END as [single user] ,
 CASE WHEN (status & 32768 ) <> 0 THEN 1 ELSE 0 END as [emergency mode] , 
 CASE WHEN (status & 4194304 ) <> 0 THEN 1 ELSE 0 END as [autoshrink] , 
 CASE WHEN (status & 1073741824 ) <> 0 THEN 1 ELSE 0 END as [cleanly shutdown] 
from master..sysdatabases

 

2nd one is more concise but less readable:

SELECT 
 name,
 status, 
 CONVERT(BIT, (status & 1 )) as [is_autoclose],
 CONVERT(BIT, (status & 4 )) as [is_select_into_bulkcopy] ,
 CONVERT(BIT, (status & 8 )) as [is_trunc_log_on_chkpt] ,
 CONVERT(BIT, (status & 16 )) as [is_torn_page_detection] ,
 CONVERT(BIT, (status & 32 )) as [is_loading] ,
 CONVERT(BIT, (status & 64 )) as [is_pre_recovery] ,
 CONVERT(BIT, (status & 128 )) as [is_recovering] , 
 CONVERT(BIT, (status & 256 )) as [is_not_recovered] ,
 CONVERT(BIT, (status & 512 )) as [is_offline] ,
 CONVERT(BIT, (status & 1024 )) as [is_read_only] ,
 CONVERT(BIT, (status & 2048 )) as [is_dbo_use_only] ,
 CONVERT(BIT, (status & 4096 )) as [is_single_user] ,
 CONVERT(BIT, (status & 32768 )) as [is_emergency_mode] , 
 CONVERT(BIT, (status & 4194304 )) as [is_autoshrink] , 
 CONVERT(BIT, (status & 1073741824 )) as [is_cleanly_shutdown] 
from master..sysdatabases



& (Bitwise AND) (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms174965.aspx

sysdatabases
http://technet.microsoft.com/en-us/library/aa260406(v=sql.80).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s