Better way of extracting the first 5 bits from bigint and convert them to int?

Question

Better way of extracting the first 5 bits from bigint and convert them to int?

muten79

SSC Veteran

Points: 214
More actions
February 24, 2011 at 10:38 am

#235736

Hi Guys
I am just seeing how to improve this.
I've got a bigint with some data inside, its first 5 bits will be used to represent an integer value.
The 'fastest' way I've found for doing this is the code below:
set statistics time on
declare @num bigint
set @num = 280384055017471 -- 0x0000ff01ffffffff
select cast(
master.sys.fn_cdc_hexstrtobin(
'0x000000000000000' + substring(
convert(varchar(16),
convert(binary(8),
@num & 0xFFFFF00000000000),
2),
1,5)
)
as int)
set statistics time off
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
-----------
15
I am not fully convinced it is the best way..
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

Only way to be sure is to write a CLR (c#) version and test it.

I wrote a T/SQL function to convert an (varchar) IP address to a bigint and compared it to a CLR version. The CLR version was a lot faster.

The probability of survival is inversely proportional to the angle of arrival.

Paul White SSC Guru Points: 150467 More actions · Answer 2

muten79 (2/24/2011)
I've got a bigint with some data inside, its first 5 bits will be used to represent an integer value.

That's not the first five bits, it's the first 2.5 bytes - 5 nybbles, if you prefer.

It's an odd arrangement, because an integer is 2 bytes - 4 nybbles.

Perhaps you could explain why this arrangement makes sense to you.

Nevertheless, a better and faster way to do what you are currently doing is:

DECLARE @num BIGINT;

SET @num = 280384055017471 -- 0x0000ff01ffffffff

SELECT CONVERT(INTEGER, CONVERT(VARBINARY(4), '0x0' + LEFT(CONVERT(CHAR(8), (CONVERT(BINARY(8), @num & 0xFFFFF00000000000)), 2), 5), 1));

Better still, don't store encoded BIGINT data in the database. Break the data out into two columns and store it separately.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

muten79 SSC Veteran Points: 214 More actions · Answer 3

Thanks, that query is much faster than mine.

:blush: You're right it's 5 nibbles..

I was just testing, I am simulating an uniqueidenfier column and needed to split the bigint into 3 parts to grant the uniqueness of the field when it is generated externally from different platforms.