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

• muten79

  SSC Veteran

  Points: 274

  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!

• sturner

  SSC-Insane

  Points: 22411

  More actions

  February 24, 2011 at 11:11 am

  #1291144

  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: 150468

  More actions

  February 24, 2011 at 10:44 pm

  #1291362

  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
  All articles available on SQL.kiwi
  @SQL_Kiwi

• muten79

  SSC Veteran

  Points: 274

  More actions

  February 25, 2011 at 2:13 am

  #1291411

  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.