Book Image

SQL Server 2014 Development Essentials

By : Basit A. Masood-Al-Farooq
Book Image

SQL Server 2014 Development Essentials

By: Basit A. Masood-Al-Farooq

Overview of this book

Table of Contents (14 chapters)
SQL Server 2014 Development Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

Common Table Expressions


A common table expression (CTE) is a temporary result set that your query can reference. You can use a CTE just as you would any other table. However, when the query ends, the CTE is deleted from the memory. We also use CTEs to create recursive queries, simplify complex query logic, and create multiple references of the same table.

To create a CTE, use a WITH clause outside the SELECT statement. The following is the basic syntax of a CTE:

WITH cte_name ([(column_name [,...n])])
AS
(CTE_query_definition)

The following is an explanation of the arguments of the CTE syntax:

  • cte_name: This is the name of the CTE you have referenced in the query

  • column_name: This is the name of the column; note that it is an optional argument

The following is an example of the structure of a CTE:

WITH    cteSalesPerson ( [SalesPersonID], [FullName], [TerritoryName], [SalesQuota], [Bonus], [CommissionPct], [SalesYTD], [SalesLastYear] )
AS ( SELECT   sp1.[BusinessEntityID] ,
sp2.[FirstName] ...