Interesting Use for Recursive Common Table Expression

Who is online?  0 guests and 2 members
Home  »  Blogs  »  timmurphy  »  Interesting Use for Recursive Common Table Expression

Interesting Use for Recursive Common Table Expression

change text size: A A A

posted 10/27/2011  by timmurphy -  Views: [26433] 


Remember when you used to have to write your times tables out? You had to because there was no such thing as a pocket calculator. It is sad that I remember that. You would get a piece of lined paper and write 1 2 3 4 5 6 7 8 9 10 horizontally and then vertically and start filling in the products in a grid. Yes that was also before you had to learn the 11 and 12 times table.
 
Well here is an easier way using a recursive CTE.
 
First, I was looking for a way to populate a table with numbers from 1 to 100 and found a simple example for a Recursive Common Table Expression. Here’s what I ended up with:
  
WITH CTE (Number)
      AS (SELECT 1
            UNION ALL
            SELECT Number + 1
            FROM CTE
            WHERE Number < 100)
SELECT * from CTE
If you try to use this with more than 100 recursions you will get an error.
 
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
 
You can use this Query Hint if you need to increase the maximum recursion:
 OPTION (MAXRECURSION <number up to 32767>)
 
After thinking about the how it works, I got to thinking there would be a way to create a multiplication table using the same idea where the header and row number would contain the multipliers and the product of each would be returned in the grid. This table goes to 13 for good measure.

WITH t ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]) AS

   (  SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13

            UNION ALL

               SELECT

                         [1] + 1

                        ,[2] + 2

                        ,[3] + 3

                        ,[4] + 4

                        ,[5] + 5

                        ,[6] + 6

                        ,[7] + 7

                        ,[8] + 8

                        ,[9] + 9

                        ,[10] + 10

                        ,[11] + 11

                        ,[12] + 12

                        ,[13] + 13

                   FROM t WHERE [1] < 13

      )

SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13] FROM t

 

times table

 

I know you may never need this (the multiplication table that is), but I hope it at least gets you thinking recursively anyway. Of course it would have been nice to have this in the 2nd Grade.

tags : TSQL, Recursive CTE
 
4.85
/5
Avg: 4.85/5: (2 votes)

Comments (2)

DustinRyan
DustinRyan said:
Interesting use of a CTE. CTE's are pretty powerful. If you have a very large parent-child type table, you'll certainly need the MAXRECURSION option.
10/27/2011
 · 
Comment
 
by
timmurphy
timmurphy said:
That's why I titled the blog "Interesting Use..." Anyone want to try creating multiplcation table up to 32767 X 32768 and see how long it takes? Thanks Dustin:)
10/27/2011
 · 
Comment
 
by
Add a new comment
Blogs RSS Feed

timmurphy's latest blog posts

Blogs RSS Feed

Latest community blog posts