Simple recursive CTE

I like CTEs. Mostly because it lets me have a derived table that I can refer to multiple times. I don't often use the recursive features of it. But when I do, I repeatedly feel amazed at the power available.

Today I had a challenge to produce a nice SQL way of converting integers to binary

So I pulled out the CTE. :)

with ctebins as
(select num as num_orig, num as working_level, cast('' as varchar(max)) as binval
from nums_bin
union all
select c.num_orig, c.working_level / 2, cast(c.working_level % 2 as varchar(max)) + c.binval
from ctebins c
where c.working_level > 0
select num_orig, binval
from ctebins
where working_level = 0

What this is doing is to populate the top level of the CTE with a bunch of numbers from nums_bin, along with a blank string. I know I don't have to use varchar(max) - it's not going to get that big after all...

Then it fills the CTE with rows using recursion. I keep the original number (to use later), and keep cutting it in half to get the binary number out. Then I query the full CTE for the last level of recursion - when my working number got down to zero.

It works nicely, and it's very fast on a big set of numbers. 


Published Fri, Oct 20 2006 20:26 by Rob Farley
Filed under: ,


Monday, October 23, 2006 8:04 PM by Mitch Wheat

# re: Simple recursive CTE

Hi Rob

I had a go at writing a procedural T-SQL version over at my blog. I wonder if it's any faster...

Monday, October 23, 2006 8:11 PM by Rob Farley

# re: Simple recursive CTE

Yes, well done. See my latest post for comments....