InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:14:54 GMT20http://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspx[quote][b]bteraberry (10/2/2012)[/b][hr]I'm not sure how logical this project is, but it seemed interesting to do this without looping. It was fun.[code="sql"]declare @b bigint = 31;declare @out varbinary(128) = 0x;-- simple inline tally tablewith cteTen as ( select N from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as t(N) ),cteNums as ( select N = 0 union all select top (63) -- the 64th bit would be bigger than a bigint max value N = row_number() over(order by (select null)) from cteTen as cte1 cross join cteTen as cte2 ),-- the meat of the querycteInit as ( select cte.N , ValThis = power(convert(decimal(20,0), 2), N) , ValNext = power(convert(decimal(20,0), 2), N + 1) from cteNums as cte ) select @out = @out + convert(varbinary(1), case when (@b - (floor(@b / cte.ValNext)) * cte.ValNext) >= cte.ValThis then 1 else 0 end)from cteInit as ctewhere cte.ValThis <= @border by cte.N desc;select @out;[/code]This is verified to work with the largest possible bigint. I did not code it with negative numbers in mind.[/quote]Wow this is way over my head :-) It works, I will runs it against the database and see how faster compare to the original code.Thanks for spending time on this. alot to learn from this code. btw, can you help me understand the logics behind this query?Thu, 11 Oct 2012 09:56:12 GMThaiao2000http://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspxI'm not sure how logical this project is, but it seemed interesting to do this without looping. It was fun.[code="sql"]declare @b bigint = 31;declare @out varbinary(128) = 0x;-- simple inline tally tablewith cteTen as ( select N from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as t(N) ),cteNums as ( select N = 0 union all select top (63) -- the 64th bit would be bigger than a bigint max value N = row_number() over(order by (select null)) from cteTen as cte1 cross join cteTen as cte2 ),-- the meat of the querycteInit as ( select cte.N , ValThis = power(convert(decimal(20,0), 2), N) , ValNext = power(convert(decimal(20,0), 2), N + 1) from cteNums as cte ) select @out = @out + convert(varbinary(1), case when (@b - (floor(@b / cte.ValNext)) * cte.ValNext) >= cte.ValThis then 1 else 0 end)from cteInit as ctewhere cte.ValThis <= @border by cte.N desc;select @out;[/code]This is verified to work with the largest possible bigint. I did not code it with negative numbers in mind.Tue, 02 Oct 2012 15:49:42 GMTbteraberryhttp://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspx[quote][b] Ahh - what I was missing was the actual requirements! :-)This may get you part-way there: http://pratchev.blogspot.com/2008/04/convert-hex-string-to-binary-string.html. You will need to efficiently deal with leading zeros though before doing the reverse/replace thing. The key is to do this as a set-based operation and not the iterative way you have now. I am confident there is a solution, but it goes beyond what I am willing to invest for free to get you there. :cool: [/quote]Not big of a deal. Believe it or not...I did this entire thing for free too :-). It wasn't my assignment. Anyway what I got so far should be good for them to start with, they should work on the fine tune it. Thanks for direction!-HaiTue, 02 Oct 2012 14:12:18 GMThaiao2000http://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspx[quote][b]haiao2000 (10/2/2012)[/b][hr][quote][b]TheSQLGuru (10/2/2012)[/b][hr]I am seriously missing something:SELECT CAST(5 AS VARBINARY(128)) --0x00000005SELECT CAST(0x00000005 AS bigint) --5SELECT dbo.ConvertBigIntToVarBinary1(5) --0x010001SELECT CAST(0x010001 AS bigint) --65537[/quote]NAH! you aren't missing anything. it was just our requirements written that way. In the nutshell, our existing application has capability of searching the varbinary field data bit-by-bit (or byte-by-byte whatever that is..) without first converingt the bit to character, that is from what I was told and understood. so this is the how conversion process works: -first convert the bigint to binary value, -reverse the entire string-foreach bit in the string, add a 0 infront of it.-then convert the value to varbinary field using this format: convert(varbinary(128),@result,2)[/quote]Ahh - what I was missing was the actual requirements! :-)This may get you part-way there: http://pratchev.blogspot.com/2008/04/convert-hex-string-to-binary-string.html. You will need to efficiently deal with leading zeros though before doing the reverse/replace thing. The key is to do this as a set-based operation and not the iterative way you have now. I am confident there is a solution, but it goes beyond what I am willing to invest for free to get you there. :cool: Tue, 02 Oct 2012 13:26:21 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspx[quote][b]TheSQLGuru (10/2/2012)[/b][hr]I am seriously missing something:SELECT CAST(5 AS VARBINARY(128)) --0x00000005SELECT CAST(0x00000005 AS bigint) --5SELECT dbo.ConvertBigIntToVarBinary1(5) --0x010001SELECT CAST(0x010001 AS bigint) --65537[/quote]NAH! you aren't missing anything. it was just our requirements written that way. In the nutshell, our existing application has capability of searching the varbinary field data bit-by-bit (or byte-by-byte whatever that is..) without first converingt the bit to character, that is from what I was told and understood. so this is the how conversion process works: -first convert the bigint to binary value, -reverse the entire string-foreach bit in the string, add a 0 infront of it.-then convert the value to varbinary field using this format: convert(varbinary(128),@result,2)Tue, 02 Oct 2012 11:13:31 GMThaiao2000http://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspxI am seriously missing something:SELECT CAST(5 AS VARBINARY(128)) --0x00000005SELECT CAST(0x00000005 AS bigint) --5SELECT dbo.ConvertBigIntToVarBinary1(5) --0x010001SELECT CAST(0x010001 AS bigint) --65537Tue, 02 Oct 2012 10:55:38 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspxSome one suggested this function works[code="sql"]ALTER function [dbo].[ConvertBigIntToVarBinary1](@param bigint)returns varbinary(1000) ASbegin declare @iPower int; declare @result varbinary(1000); declare @powerOf2 bigint; set @result = 0x; set @powerOf2 = 1; set @iPower = 0; while @powerOf2 <= @param begin set @result = @result + CAST(@param / @powerOf2 % 2 AS binary(1)); set @iPower = @iPower + 1; set @powerOf2 = power(2., @iPower); end return @resultend[/code]Although I dont see much advance of it plus it fails to convert the max bigint value for some reasonsSELECT dbo.ConvertBigIntToVarBinary1(9223372036854775807)Mon, 01 Oct 2012 08:31:04 GMThaiao2000http://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspxYes there is, if you run the function and pass a parameter like 5, you will see the result. Just perform a normal conversion would not give the desire resultFri, 28 Sep 2012 21:18:41 GMThaiao2000http://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspxIs there a reason why you couldn't just use CAST(SomeBigIntColumn AS VARBINARY(128))?Fri, 28 Sep 2012 20:40:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1366123-391-1.aspxHello Expert.I need to convert data from an existing column from BigInt to VarBinary type. I wrote the function to do the conversion; the function is fairly fast, however, it is not fast enough to use on a table with more than 40 millions records (takes about 27minutes). Could someone help optimize it so that it runs a bit faster? Thanks in advance,-Hai [code="sql"]ALTER function [dbo].[ConvertBigIntToVarBinary]((c)param BigInt)returns varbinary(128) ASbegin declare (c)result varchar(128) set (c)result = ''while 1=1 begin select (c)result= convert(char(1),(c)param % 2)+(c)result, (c)param = convert(bigint, ((c)param / 2)) if (c)param = 0 return convert(varbinary(128), replace(replace(reverse((c)result),'0','00'),'1','01'),2) end return convert(varbinary(128),'00',2)end[/code]Fri, 28 Sep 2012 15:03:43 GMThaiao2000