SQL Common Table Expressions

I’ve been working with Microsoft SQL Server Common Table Expressions (CTE) lately and am totally impressed every time.

My most recent experience was needing a temporary table with the fixed number of rows into which I would load some data based on the row number (basically).  There are several ways to accomplish this with T-SQL.  The most common is a loop such as this:

DECLARE @Test TABLE (Counter INT)
DECLARE @Count INT
SET @Count = 0

WHILE (@Count < 100)
BEGIN
  SET @Count = @Count + 1
   
  INSERT @Test (Counter)
  VALUES (@Count)

END
 
SELECT * FROM @Test 

The above works but is far from elegant.  Using a Common Table Expression, the same thing can be accomplished with this:

WITH cte AS
(SELECT 1 AS count
  UNION ALL
 SELECT count + 1
   FROM cte
  WHERE count < 100
)
SELECT count
  FROM cte OPTION (MAXRECURSION 100)

You can use this query to populate a table by modifying the above to Insert the results into a table variable, temporary table, or a table.  This examples uses a table variable.

DECLARE @Test TABLE (Counter INT);

WITH cte AS
(SELECT 1 AS count
  UNION ALL
 SELECT count + 1
   FROM cte
  WHERE count < 100
)
INSERT @Test (Counter)
SELECT count
  FROM cte OPTION (MAXRECURSION 100)

From a performance perspective:
Loop count of 30: While loop requires 0 milliseconds. The CTE requires 0 milliseconds.
Loop count of 10,000: While loop requires 403 milliseconds. The CTE requires 167 /milliseconds
Loop count of 32,767: While loop requires 790 milliseconds. The CTE requires 540 milliseconds.
Loop count of 500,000: While loop requires 9.34 seconds. The CTE requires 8.097 seconds.

My conclusion is that on smallish counts (< 5000) the performance is about the same. On large to very large counts (> 5000 AND < 500,000) the Common Table Expressions approach is faster.  On very large counts, While and Common Table Expressions are again pretty similar.  I have no idea why this is but I did find it interesting.  I suspect my ability to record performance and machine resources may have a lot to do with it so your mileage may vary based on your environment.

Common Table Expressions are very powerful, very cool, and here to stay – learn to take advantage of them!