Common table expressions (CTE's)

• CTE table names are declared with the prefix cte
• Used to simplify complex joins and subqueries.
• Use a Common Table Expression for paging instead of Dynamic SQL.
• Always start with a semi-colon before the WITH.
• Chaining CTE's must be limited to 3 instances.
• CTE's must be filtered as soon as possible to limit the number of records stored in memory.
• CTEs can only be used when data is only required for a single use in the procedure.
• CTEs must always be provided named column and not use the * selector.

;WITH cteEmployees
     AS (SELECT
                Name
              , Surname
              , EmployeeNumber
         FROM
              prsEmployees WITH(NOLOCK))
     SELECT
            *
     FROM
          cteEmployees

The use of recursive CTEs

• Always ensure a termination condition is defined.
• For an example view this site
• e.g.

WITH Managers AS
(
--initialization
SELECT EmployeeID, LastName, ReportsTo 
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
--recursive execution
SELECT e.employeeID,e.LastName, e.ReportsTo
FROM Employees e INNER JOIN Managers m 
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers