tag:blogger.com,1999:blog-29286778.post116118279896802414..comments2023-07-02T05:53:35.132-04:00Comments on SQL Garbage Collector: A Scenario to Ponder #5Unknownnoreply@blogger.comBlogger7125tag:blogger.com,1999:blog-29286778.post-86902564871809349812006-10-23T08:47:00.000-04:002006-10-23T08:47:00.000-04:00Here is my solution using the common table express...Here is my solution using the common table expressions. Definitely not as brainy as the one given by Mitch. But works fine :)<br /><br />Assuming nums_bin is the table having the 50000 records, the following is the query<br /><br />declare @maxval int<br />select @maxval = max(num) from nums_bin;<br />with cte as<br />(<br /> select 1 as bin,1 as counter<br /> union all<br />select bin*2,counter + 1 from cte<br />where bin*2 < @maxval<br />)<br />select num,<br />(select (num&bin)/bin from cte b order by counter desc for xml path('')) as bin <br />from nums_binOmnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-92177375086793943562006-10-23T08:43:00.000-04:002006-10-23T08:43:00.000-04:00Hi Mitch,
Its a wonderful implementation. Somet...Hi Mitch,<br /> Its a wonderful implementation. Something I have never come across till date. Its pretty much as fast as it can get. It took just a second for processing 50,000 records, the time I would attribute to sending data over the network.<br /><br />Great One.<br />-OmniOmnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-16078944303313144902006-10-23T05:59:00.000-04:002006-10-23T05:59:00.000-04:00How about this:
CREATE FUNCTION IntegerToBinarySt...How about this:<br /><br />CREATE FUNCTION IntegerToBinaryString(@intval int)<br /><br />RETURNS char(32)<br /><br />AS<br /><br />BEGIN<br /><br />DECLARE @bincode char(64)<br /><br />SET @bincode = '0000000100100011010001010110011110001001101010111100110111101111'<br /><br />RETURN ( <br /><br />SUBSTRING( @bincode, (@IntVal / 268435456) * 4 + 1, 4 ) +<br /><br />SUBSTRING( @bincode, ((@IntVal / 16777216) & 15) * 4 + 1 , 4 ) +<br /><br />SUBSTRING( @bincode, ((@IntVal / 1048576) & 15) * 4 + 1 , 4 ) +<br /><br />SUBSTRING( @bincode, ((@IntVal / 65536) & 15) * 4 + 1 , 4 ) +<br /><br />SUBSTRING( @bincode, ((@IntVal / 4096) & 15) * 4 + 1 , 4 ) +<br /><br />SUBSTRING( @bincode, ((@IntVal / 256) & 15) * 4 + 1 , 4 ) +<br /><br />SUBSTRING( @bincode, ((@IntVal / 16) & 15) * 4 + 1 , 4 ) +<br /><br />SUBSTRING( @bincode, (@IntVal & 15) * 4 + 1 , 4 ) <br /><br />)<br /><br />END<br /><br />GO<br /><br />There's more info over at my blogAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1161418680045643112006-10-21T04:18:00.000-04:002006-10-21T04:18:00.000-04:00Hi all (those who are following this), The soluti...Hi all (those who are following this),<BR/> The solution provided by Rob takes 37 seconds in my server box to return the result for 50,000 numbers. So far the best I have. Any one to challenge that?<BR/><BR/>Rob, you want to take another stab at it :)<BR/>-OmniOmnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1161414430960093492006-10-21T03:07:00.000-04:002006-10-21T03:07:00.000-04:00Hi Rob, Beautiful implementation. I was thinking...Hi Rob,<BR/> Beautiful implementation. I was thinking in the same lines for the implementation.<BR/>But then I thought, if I extend it to work on, say, a million rows, I would end up caching so much data. Since I would be appending a million rows for every iteration. What do you think?<BR/><BR/>And I read through your post in your blog. If you like recursive ctes then you may be interested in this blog post (in case you hadn't already seen it) :)<BR/><BR/>http://omnibuzz-sql.blogspot.com/2006/06/interesting-queries-using-recursive.html<BR/><BR/>-OmniOmnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1161332813910578812006-10-20T04:26:00.000-04:002006-10-20T04:26:00.000-04:00Actually, you don't need nums0. That had been base...Actually, you don't need nums0. That had been based on an earlier incarnation I had.<BR/><BR/>with ctebins as<BR/>(select num as num_orig, num as working_level, cast('' as varchar(max)) as binval<BR/>from nums_bin<BR/>union all<BR/>select c.num_orig, c.working_level / 2, cast(c.working_level % 2 as varchar(max)) + c.binval<BR/>from <BR/> ctebins c<BR/>where c.working_level > 0<BR/>)<BR/>--And grab the records from the CTE where the working_level is 0<BR/>select num_orig, binval<BR/>from ctebins<BR/>where working_level = 0<BR/>;Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1161331314670429942006-10-20T04:01:00.000-04:002006-10-20T04:01:00.000-04:00:) Nice question.Assuming my nums0 table (which st...:) Nice question.<BR/><BR/>Assuming my nums0 table (which starts at 0 rather than 1).<BR/><BR/>--First populate my nums_bin table - values which are going to be converted to binary<BR/>select num<BR/>into dbo.nums_bin<BR/>from nums0 <BR/>where num in (543080,667997,815717,901825);<BR/><BR/>--Now use a CTE to recursively go through my nums0 table, extending the binval field each time...<BR/>with ctebins as<BR/>(select num as num_orig, num as working_level, cast('' as varchar(max)) as binval<BR/>from nums_bin<BR/>union all<BR/>select c.num_orig, n.num, cast(c.working_level % 2 as varchar(max)) + c.binval<BR/>from nums0 n<BR/> join<BR/> ctebins c<BR/> on n.num = c.working_level / 2<BR/> and c.working_level > 0<BR/>)<BR/>--And grab the records from the CTE where the working_level is 0<BR/>select num_orig, binval<BR/>from ctebins<BR/>where working_level = 0<BR/>;Anonymousnoreply@blogger.com