Common Table Expression in SQL Server

Common Table Expressions are introduced in SQL Server 2005. They are very useful when we are working with sub queries.

Sub queries are cumbersome to use when all the filtering and matching logic has to be implemented into the sub query expressions. Sub queries are not reusable. Data in sub query is available till it gives the result set to the user. Sub queries are useful when we have a single task to perform and need to use the query only once.

A work around to the limitation of sub query is to create and populate the sub query contents into temporary table. But, it needs permissions to create and use. We can use this temporary table as a physical table and we can apply join and filtering expressions on this. But, it is costly with respect to performance and it is overhead to physical disk reads.

CTE is best compared to both sub queries and temporary tables. It is a named object which can be reused and referenced like a physical table which gives flexibility to perform operations. It is a sub query that exists only in memory and does not require permissions to use and won’t perform unnecessary disk operations.  

Syntax for creating CTE:

WITH <CTE_Name> (<Col1>,<Col2>,…)

AS

( <Select Statement> )

 

CTE should be defined before it is used in the query. It should start with the key word WITH and the output column names list with in the parentheses and followed by SELECT statement.

Following statement selects all the male customers who are married. (I am taking AdventureWorksDW2012 for the demo purpose) -

WITH Customer_Details ([CustomerKey],[Customer_Full_Name])

AS

(select [CustomerKey],[FirstName]+' '+

      ISNULL([MiddleName],'') +' '+

      ISNULL([LastName],'') as [Customer_Full_Name]

         from [AdventureWorksDW2012].[dbo].[DimCustomer]

         where [Gender]='M' and [MaritalStatus]='M')

 

 

Above script alone will not run by itself. We have to use it in the subsequent query. If we try to execute the query, it throws invalid syntax error message. After defining the CTE, we can use it as a table. Columns that are used for joining, filtering and sorting outside the CTE, should be defined in the output column list with in parentheses.

Following Statement will give total quantities sold by county and promotion.

WITH Customer_Details ([CustomerKey],[Customer_Full_Name])

AS

(select [CustomerKey],[FirstName]+' '+

      ISNULL([MiddleName],'') +' '+

      ISNULL([LastName],'') as [Customer_Full_Name]

         from [AdventureWorksDW2012].[dbo].[DimCustomer]

         where [Gender]='M' and [MaritalStatus]='M')

 

  Select

  ST.[SalesTerritoryCountry] as Country,

  DP.[EnglishPromotionName] as Promotion_Name,

  sum(IST.[OrderQuantity]) as Sold_Quantity

  from  [AdventureWorksDW2012].[dbo].[DimSalesTerritory] as ST

  inner join [FactInternetSales] as IST

  on IST.[SalesTerritoryKey]=ST.[SalesTerritoryAlternateKey]

  inner join Customer_Details as cd

  on cd.[CustomerKey]=IST.[CustomerKey]

  inner join [AdventureWorksDW2012].[dbo].[DimPromotion] DP

  on DP.PromotionKey=IST.PromotionKey

  group by [SalesTerritoryCountry],DP.[EnglishPromotionName]

  order by 1

 

Output is –

Country	Promotion_Name	Sold_Quantity
Australia	No Discount	3591
Australia	Touring-3000 Promotion	1
Australia	Volume Discount 11 to 14	139
Canada	No Discount	2542
Canada	Touring-1000 Promotion	1
Canada	Touring-3000 Promotion	1
Canada	Volume Discount 11 to 14	92
France	No Discount	1308
France	Volume Discount 11 to 14	48
Germany	No Discount	1383
Germany	Touring-3000 Promotion	1
Germany	Volume Discount 11 to 14	46
United Kingdom	No Discount	1866
United Kingdom	Touring-1000 Promotion	1
United Kingdom	Volume Discount 11 to 14	75
United States	No Discount	6189
United States	Touring-1000 Promotion	4
United States	Touring-3000 Promotion	1

 

Let’s discuss how to use CTE as recursive in the next post.

Happy Learning.