World's Simplest Recursive CTE?
At SQL PASS this year, I did a session about Common Table Expressions in SQL Server 2005. I had endeavored to come up with an extremely simple recursive CTE, but wasn’t creative enough at the time.
But I revisited the problem in preparation for an updated CTE session at DevConnections this week, 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 probably what I’ll show this week. 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!’