When to use Common Table Expression (CTE)

by

One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.

An example of self referencing is recursion: Recursive Queries Using CTE

For exciting Microsoft definitions
Taken from Books Online:

A CTE can be used to:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

  • Reference the resulting table multiple times in the same statement.

More Related Contents:

  1. Difference between CTE and SubQuery?
  2. Update records in table from CTE
  3. Common Table Expression, why semicolon?
  4. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  5. CTE error: “Types don’t match between the anchor and the recursive part”
  6. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  7. How to create a specific SQL Server stored procedure? [closed]
  8. SQL Server dynamic PIVOT query?
  9. SQL Server – Best way to get identity of inserted row?
  10. Should I use != or for not equal in T-SQL?
  11. How to update two tables in one statement in SQL Server 2005?
  12. SQL Server Process Queue Race Condition
  13. How should I pass a table name into a stored proc?
  14. Is the NOLOCK (Sql Server hint) bad practice?
  15. SQL – Subtracting a depleting value from rows
  16. How to drop SQL default constraint without knowing its name?
  17. Sequence vs identity
  18. Dynamic update statement with variable column names
  19. How to select only the first rows for each unique value of a column?
  20. SQL Server (TSQL) – Is it possible to EXEC statements in parallel?
  21. Unordered results in SQL
  22. Using PIVOT in SQL Server 2008
  23. How to find column names for all tables in all databases in SQL Server
  24. SQL Row_Number() function in Where Clause
  25. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  26. How do I compare 2 rows from the same table (SQL Server)?
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Group consecutive rows of same value using time spans
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment