Introduction

The common table expression is one of the new features in SQL Server 2005. It can be used instead of temp table or table variables in the stored procedures in the circumstances. Let's see CTE with some example queries.

Background

Most of the developers while writing the stored procedures create the temp tables or table variables. They need some table to store the temporary results to manipulate the data in the other tables based on this temporary result.

The temp variables will be stored on the tempdb and they need to be deleted in the tempdb database.

The table variable is best when compared with the temp tables. Because the table variable initially will be there in the memory for a certain limit of size and if the size increases then it will be moved to the temp database. However, the scope of the table variable is only up to that program. When compared with the table variable the CTE is best. It just stores the result set like a normal view.

CTE (common table expression)

The CTE is one of the essential features in the SQL server 2005. It just stores the result as a temp result set. It can be accessed like a normal table or view. This is only up to that scope.

The syntax of the CTE is the following.

WITH name (Alias name of the retrieve result set fields) AS 
(
    -- Write the SQL query here
)
SELECT * FROM name

Here the select statement must be very next to the CTE. The name is mandatory and the argument is optional. This can be used to give the alias to the retrieve field of the CTE.

CTE 1. Simple CTE

WITH ProductCTE AS 
(
  SELECT ProductID AS [ID], ProductName AS [Name], CategoryID AS [CID], UnitPrice AS [Price]
  FROM Products
)
SELECT * FROM ProductCTE

Here all the product details like ID, name, category ID, and Unit Price will be retrieved and stored as temporary results set in the ProductCTE.

This result set can be retrieved like a table or view.

CTE2. Simple CTE with alias

WITH ProductCTE(ID, Name, Category, Price) AS 
(
  SELECT ProductID, ProductName, CategoryID, UnitPrice
  FROM Products
)
SELECT * FROM ProductCTE

Here there are four fields retrieved from the Products and the alias name has been given in the document to the CTE result set name.

It also accepts the following as it is in the normal select query.

WITH ProductCTE AS 
(
  SELECT ProductID AS [ID], ProductName AS [Name], CategoryID AS [CID], UnitPrice AS [Price]
  FROM Products
)
SELECT * FROM ProductCTE

CTE 3. CTE joins with normal table

The result set of the CTE can be joined with any table and also can enforce the relationship with the CTE and other tables.

WITH OrderCustomer AS 
(
  SELECT DISTINCT CustomerID FROM Orders
)
SELECT C.CustomerID, C.CompanyName, C.ContactName, C.Address + ', ' + C.City AS [Address] 
FROM Customers C 
INNER JOIN OrderCustomer OC ON OC.CustomerID = C.CustomerID

Here the Ordered Customers will be placed in the CTE result set and it will be joined with the Customer's details.

CTE 4. Multiple resultsets in the CTE

WITH MyCTE1 AS 
(
  SELECT ProductID, SupplierID, CategoryID, UnitPrice, ProductName FROM Products
),
MyCTE2 AS 
(
  SELECT DISTINCT ProductID FROM [Order Details]
)
SELECT C1.ProductID, C1.ProductName, C1.SupplierID, C1.CategoryID 
FROM MyCTE1 C1 
INNER JOIN MyCTE2 C2 ON C1.ProductID = C2.ProductID

Here, two result sets will be filtered based on the join condition.

CTE 5. Union statements in the CTE

WITH PartProdCateSale AS
(
  SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Condiments')
  UNION ALL
  SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName='Seafood')
)
SELECT OD.ProductID, SUM(OD.UnitPrice*OD.Quantity) AS [Total Sale] 
FROM [Order Details] OD 
INNER JOIN PartProdCateSale PPCS ON PPCS.ProductID = OD.ProductID
GROUP BY OD.ProductID

Normally when we combine the many result sets we create a table and then insert it into that table. But see here, we have combined with the union all and instead of a table, CTE has been used.

CTE 6. CTE with identity column

WITH MyCustomCTE AS
(
  SELECT CustomerID, ROW_NUMBER() OVER (ORDER BY CustomerID) AS iNo 
  FROM Customers
)
SELECT * FROM MyCustomCTE

Conclusion

I hope that the CTE will be very useful in the SQL server. When we work the web applications the performance must be considered in the both front-end and Back-end. First, we must retrieve the result effectively in the back end. Please feel free to post your valuable comments and suggestions about this article.