CTE Christmas tree

Just went to Audrey's T-SQL Awesomeness at SQL Pass for some enthusiastic pointers on writing awesome and cool code :) I really loved to see how excited she was about writing readable, functional but smart looking code.

She talk mostly about CTE (Common Table Expressions) and she showed how to make recursive functions. She had a simple ex. which produce a 25 records each with one more + using recursive CTE. She said she wanted to make a Christmas tree but could figure out how. So I though I would give it a go :)




with ChristmasTree (RowNumber, TreeData) as
(
/* Select the top - this is the anchor */
select 0 as RowNumber, cast(replicate(' ', 25)+'*'+replicate(' ', 25) as varchar(51)) as TreeData
union all
/* Select the rest from it self until we have 25 rows (plus the top)
  For each time it picks up one more star on each side of the tree
  This is the recursive menber */
select RowNumber+1 as RowNumber, cast(replicate(' ', 25-RowNumber)+replicate('*',RowNumber+1)+replicate(' ', 25-RowNumber) as varchar(51)) as TreeData
from ChristmasTree
where RowNumber < 25
)
/* Statement to fire it all up */
select TreeData from ChristmasTree

The last statement is the one firing up under the recursive call. It call into ChristmasTree (CTE).

The first statement call in the CTE is the anchor. It needed to lay the foundation of the recursiveness.

The statement below is call. And was it really does is sending RowNumber and TreeData into the next recursive call. So the next call has RowNumber 2 and so on. This keeps happening until RowNumber hits 25.

If you remove the where part it will keep going until it has nested down for 32767 (signed int max number) and then it breaks. Well it might stop after 100 iteration which is SQL server default recursion deept.

But this is a truly powerful tool to get for instance all children from a parent throug multible levels.

Like Audrey would say. That is awesome :)