THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Bitmask Handling, part 4: Left-shift and right-shift

This blog has moved! You can find this content at the following new location:

http://dataeducation.com/bitmask-handling-part-4-left-shift-and-right-shift/

Published Thursday, February 10, 2005 12:31 PM by Adam Machanic

Comments

 

saisub said:

no info regarding signed number shift..bye

March 26, 2007 11:47 PM
 

Adam Machanic said:

Sorry, I kind of dropped the ball on this over two years ago.  Some day perhaps I'll revisit it, but no promises.  I'll probably use SQLCLR if I do.

May 22, 2007 2:05 PM
 

Stephen Channell said:

Have I missed something.. but bit shifting can be done with the power() function.. e.g. 100 left shift 3 and right shift 3

select 100 * power(2,3), convert(int, 100 / power(2,3))

May 22, 2008 8:58 AM
 

Justin Dearing said:

Stephen,

Yes you can. and I did that to do byte shifting like so:

SELECT

CAST(0xFF * POWER(CAST(256 AS BIGINT), 0) AS BINARY(12)) AS Shift0,

CAST(0xFF * POWER(CAST(256 AS BIGINT), 1) AS BINARY(12)) AS Shift1,

CAST(0xFF * POWER(CAST(256 AS BIGINT), 2) AS BINARY(12)) AS Shift2,

CAST(0xFF * POWER(CAST(256 AS BIGINT), 3) AS BINARY(12)) AS Shift3,

CAST(0xFF * POWER(CAST(256 AS BIGINT), 4) AS BINARY(12)) AS Shift4,

CAST(0xFF * POWER(CAST(256 AS BIGINT), 5) AS BINARY(12)) AS Shift5

July 31, 2010 10:26 PM
 

Adam Machanic said:

Stephen and Justin:

Yes, that will work, but since we lack unsigned types in T-SQL you can wind up with an overflow exception if you do it like that. Anyway, these techniques are way outdated today thanks to the inclusion of SQLCLR in SQL Server 2005/2008--I certainly don't recommend actually doing this stuff for production purposes :-)

August 1, 2010 3:01 PM
 

Dawid said:

Hi,

I stumbled upon your series of handling shifts in SQL, however I have spent hours already and can't implement it. Basically I need to be able to implement left-shift for integers, just like JavaScript's   77 << 5 should give me 2464, but I can't get it to work with your functions.

I have copied leftshift and splitbitmask but running leftshift funcion with (77,5) only gives me 0x

If you're still maintaining this site and could help that would be fantastic

October 20, 2015 5:17 AM
 

Adam Machanic said:

@Dawid,

These functions don't take integers as their inputs -- rather they take bitmasks. But you really should not use them for anything production related. I wrote them just for fun.

What do you need this for? As Stephen Channell mentioned above, simple bit shifting can be done with the power() function: SELECT 77 * POWER(2,5) -- that's the same as left shifting 5 times.

That won't scale to HUGE numbers. If you need to do that, I'd write a SQLCLR function.

--Adam

October 20, 2015 5:19 PM
 

In Short ... said:

for positive ints:

a << b = a * power(2, b)

a >> b = a / power(2, b)

November 9, 2015 12:51 AM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.
Privacy Statement