BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Common Table Expressions

Avoiding temporary tables and cursors for queries can lead to scripts that are easier to understand and maintain, and that provide better performance than the cursor-based alternative. One option for complex queries is the use of common table expressions.

Creating a Value Range with a Common Table Expression

A second use of CTEs is to generate a range of values where the individual items do not necessarily exist in the database. This can be combined with database information to create results that would be difficult to achieve otherwise.

As an example, consider the sample script below. Here the anchor member specifies a date, which is the first date in the range being generated. The recursive member repeatedly returns the next day's date, until the value in the WHERE clause is reached, when the CTE terminates. The main query combines the generated date range with the information in the Sales table, showing the total sales value for each date. The benefit of the CTE is that dates when no sales were made are still included in the results.

WITH DateRange (SaleDate)
AS
(
    SELECT convert(SMALLDATETIME, '2012-06-11')	
    
    UNION ALL

    SELECT
        dateadd(d, 1, R.SaleDate)
    FROM
        DateRange R
    WHERE
        SaleDate < convert(SMALLDATETIME, '2012-06-26')
)
SELECT
    convert(VARCHAR, R.SaleDate, 111) AS SaleDate,
    sum(isnull(S.Value,0)) AS DateSales
FROM
    DateRange R
LEFT JOIN
    Sales S
ON
    R.SaleDate = S.SaleDate
GROUP BY
    R.SaleDate

/* RESULTS

SaleDate     DateSales
------------ ---------
2012/06/11   1295.00
2012/06/12   0.00
2012/06/13   0.00
2012/06/14   0.00
2012/06/15   0.00
2012/06/16   1590.00
2012/06/17   0.00
2012/06/18   0.00
2012/06/19   0.00
2012/06/20   0.00
2012/06/21   1095.00
2012/06/22   895.00
2012/06/23   0.00
2012/06/24   195.00
2012/06/25   0.00
2012/06/26   395.00

*/

Infinite Loops

When you create recursive queries, as with all recursive code, you run the risk of creating infinite loops. The sample script below shows such a situation. Here the recursive member has no WHERE clause so will always return a result. This means that there is always a starting point for a further iteration.

WITH DateRange (SaleDate)
AS
(
    SELECT convert(SMALLDATETIME, '2012-06-11')
	
    UNION ALL

    SELECT dateadd(d, 1, SaleDate) FROM DateRange
)
SELECT SaleDate FROM DateRange

If you run the above script it will not actually run indefinitely. After one hundred iterations of the recursive member, an error is displayed and the CTE stops. This early termination is built in to SQL Server to prevent illegal queries running forever and consuming resources.

The statement terminated.
The maximum recursion 100 has been exhausted before statement completion.

Sometimes a CTE running for one hundred iterations does not indicate a faulty query, so you would wish to allow more iterations. In other circumstances you would not want to allow a query to execute for a fraction of this number. You can change the number of iterations at which the query automatically terminates using the MAXRECURSION option. This is added after the main query that uses the CTE data. The number of iterations can be set to any integer value up to 32,767.

The following query restricts the number of iterations to ten. This means that there are eleven results in the virtual table. Ten are from the recursive member and one from the anchor member.

WITH DateRange (SaleDate)
AS
(
    SELECT convert(SMALLDATETIME, '2012-06-11')
	
    UNION ALL

    SELECT dateadd(d, 1, SaleDate) FROM DateRange
)
SELECT SaleDate FROM DateRange
OPTION(MAXRECURSION 10)
23 June 2012