CTE (Common Table Expression) and MAXRECURSION

CTE (Common table expression) is a temporary result set that is defined for scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE can be understand as,  similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE can be self-referencing and we can also refer CTE for multiple times in the same query.

Some basic use of  CTE are;

Ø  For create a recursive query.

Ø  An alternate for view when the general use of a view is not required, in simple words when there is no need to store the definition in metadata.

Ø  CTE will enable grouping by a column from a scalar sub select, or a function that is either not deterministic or has external access.

Ø  CTE can be referred multiple times in the same statement.

CTE improves readability and ease in maintenance of complex queries. A complex query can be divided into multiple simple, queries. These small queries can then be used to generate more complex CTEs till we get final result.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.


For Example

WITH Test (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)

SELECT * FROM test

MAXRECURSION :  This option  limit the no of recursion for your CTE, thus will protect the infinite loop.

CTE With MAXRECURSION

WITH Test (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)

SELECT *
FROM test OPTION (MAXRECURSION 5)

Hope you like this small articles and surely you are going to make use of the CTE and MAXRECURSION. Please provide your valuable comments on the same.

Thanks

Anil Kumar Pandey