World's Simplest Recursive CTE?

While preparing for a SQL PASS a year or two ago when I was doing a session about common table expressions, I had endeavored to come up with an extremely simple recursive CTE. Unfortunately, I wasn’t creative enough at the time to come up with anything, so the attendees had to sit through more practical examples.

But later I revisited the problem, and came up with this:

WITH SimpleCTE(Number) AS
(
     SELECT 1
     UNION ALL
     SELECT * FROM SimpleCTE WHERE 0=1
)
SELECT * FROM SimpleCTE

No guarantees that I couldn’t get it even simpler, but this is the simplest I've ever seen. There may be another option for the SELECT * part of the CTE, but I’m not seeing it right now.

BUT! Obviously, this is the result of a thought experiment, and is not practical for anything else I can think of. Don’t take this as any kind of recommended practice!

Published 18 Aug 2009 3:47 PM by DonK
Filed under: , ,

Comments

# re: World's Simplest Recursive CTE?@ Friday, September 04, 2009 2:26 PM

I modified your code slightly.  This is slightly more complex, but also a better example:

WITH SimpleCTE(Number) AS

(

    SELECT 1

    UNION ALL

    SELECT Number+1 FROM SimpleCTE WHERE Number<10

)

SELECT * FROM SimpleCTE

by Sanford

# re: World's Simplest Recursive CTE?@ Friday, September 04, 2009 5:56 PM

Sanford,

Yep, I like the changes. Not the simplest, but indeed a better example.

In defense, my goal was to be the simplest. :-)

Thanks!

Don

by DonK