Skip to main content

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
Back to top