Bit operations in Transact SQL

by | Aug 29, 2013

Recently I was caught off guard by the idiosyncrasies of T-SQL’s bit operations.
Programmers accustomed to other languages will be a bit surprised by the absence of bitshift operators in T-SQL. Books Online has a less than helpful article on bitwise operators.
The lengths of binary and varbinary data types are in bytes.  So you might expect that binary(4) holds the same data as int, and binary(8) the same as bigint.  This is more or less accurate, except that int and bigint use their highest order bit to signify positive or negative.
With binary variables less than 4 you can safely use a lot of operands with integers or number literals : +,-,/,*,%

print '0x01 * 2 = '+ cast ((0x01 * 2) as varchar)
print '0x02 * 2 = '+ cast ((0x02 * 2) as varchar)
print '0xFF * 2 = '+ cast ((0xFF * 2) as varchar)
print '0xFFFFFF * 2 = '+ cast ((0xFFFFFF * 2) as varchar)


0x01 * 2 = 2
0x02 * 2 = 4
0xFF * 2 = 510
0xFFFFFF * 2 = 33554430
0x01 % 2 = 1
0x02 % 2 = 0
0xFF % 2 = 1
0xFFFFFF % 2 = 1

Once your binary variables are as large or larger than INT these operators stop doing what you expect:
print '0xFFFFFFFF * 2 = '+ cast ((0xFFFFFFFF * 2) as varchar)
print '0xFFFFFFFFFF * 2 = '+ cast ((0xFFFFFFFFFF * 2) as varchar)
print '0xFFFFFFFF % 2 = '+ cast ((0xFFFFFFFF % 2) as varchar)
print '0xFFFFFFFFFF % 2 = '+ cast ((0xFFFFFFFFFF % 2) as varchar)


0xFFFFFFFF * 2 = -2
0xFFFFFFFFFF * 2 = -2
0xFFFFFFFF % 2 = -1
0xFFFFFFFFFF % 2 = -1

Converting or casting data types doesn’t help much.  Bigint is only 8 bytes, and there are some interesting behaviours around 2^32:
declare @int int
set @int = 0xFF
print '@int = '+ cast (@int as varchar)
set @int = 0x0FFFFFFF
print '@int = '+ cast (@int as varchar)
declare @binary binary(4) = 0xFFFFFFFF
set @int = @binary
print '@int = '+ cast (@int as varchar)
declare @bigint bigint
set @bigint = @binary
print '@bigint = '+ cast (@bigint as varchar)


@int = 255
@int = 268435455
@int = -1
@bigint = 4294967295

Notice that 2^32 – 1 is different when you assign it to a bigint rather than an int.  Generically, numeric literals aren’t safe when working with binary data and bigints.
You can get some mileage by manual enforcing types:
declare @mybigint bigint
declare @myoperand bigint
declare @mycomparison bigint
set @mybigint = 0xFF
set @myoperand = 2
set @mycomparison = 0
if (@mybigint % @myoperand = @mycomparison) print 'even'
else
print 'odd'

rather than:

declare @mybigint bigint
set @mybigint = 0xFF
if (@mybigint % 2 = 0) print 'even'
else
print 'odd'

Conversion from BINARY to NUMERIC isn’t supported:
declare @numeric numeric(38,0)
set @numeric = 0xFF


Msg 8114, Level 16, State 5, Line 2
Error converting data type varbinary to numeric.

So what can you do with large binaries in TSQL, other than store them and do the processing in another language?
Strangely, many string functions work on binary types.  The following code converts a BINARY data type to a varchar of ones and zeros.
declare @in varbinary(128) = 0x112233445566778899AABBCCDDEEFF
declare @out varchar(1024) = ''
declare @current int
declare @BytePosition int
declare @BitPosition int</code>
set @BytePosition = len(@in)
WHILE( 0 < @BytePosition )
BEGIN
SET @current = substring(@in,@BytePosition,1)
set @BitPosition = 1
WHILE (@BitPosition <= 8 ) BEGIN
set @out = @out + rtrim(ltrim(cast((@current % 2) as varchar)))
set @current = @current / 2
set @BitPosition = @BitPosition + 1
END
SET @BytePosition = @BytePosition - 1
END
set @out = reverse(@out)
print @out

So, when you need to work with binary types in T-SQL and you can’t be sure that they are less than 8 bytes, then you need to break them up and handle them in sections.

0 Comments

Trackbacks/Pingbacks

  1. Bit operations in Transact SQL – Steven Ensslen - […] Read the rest on the Optimal BI blog. […]
Submit a Comment

Your email address will not be published. Required fields are marked *