If you have not used CTE in any of your SQL server projects, you've been missing a good tool. CTE, also known as Common Table Expression, is a new construct introduced early in Microsoft SQL Server 2005, and it becomes popular when SQL server 2008 came out since the performance had been improved in this version.
Basically, we can use it to return a particular subset of data or aggregate data across many tables, and do more manipulations on the subset. However, one might ask, it sounds like doing a subquery(See Fig 1). Well, it is true, but CTE can be encapsulated and have multiple references to it, and unlike subquery, you have choices of using CTE as non-recursive just like subquery or recursive. Best of all, it is more maintainable than subquery.
Fig 1: An example of subquery
SELECT Ord.SalesOrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice) FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord
When using CTE, complex SQL statement can be made to easier to understand format that you or other developer can still follow the logic of the what it is intended to do. From years of experience, you know that your requirement is gradually more complex and in this way, the query would be better maintainable.
Temporary Table vs CTE
In a way, CTE is similar to temporary table that it can be logical separated. However, CTE is a temporary resultset that does not physically created in the Tempdb database like the temporary table does. So if exists in memory and cannot be indexed. In addition, table variable is also like the temporary table which is created in the Tempdb database.
Here is a simple example of CTE, where CTEReports is the name of your temporary resultset
;With CTEReports(EmpID, MgrID) AS
(
Select EmployeeID, ManagerID
From Employees
Where ManagerID is NULL
)
Select * from CTEReports
Please note that you should always place a ";" semicolon in front of "With" statement as a good habit.
Simple Example of Recursive CTE
There are two blocks of code within the With statement called the Anchor block and Recursive block.
;WITH Numbers AS
(
SELECT n = 1 <------- first block
UNION ALL
SELECT n + 1 <------- begins of second block
FROM Numbers
WHERE n+1 <= 10
)
select * from Numbers
The anchor block is what defined the temporary resultset, and the recursive block is the one using the resultset.
So first block has 1, and in the second block it increases by +1, and loop until it is less than or equals to 10.
Basically, we can use it to return a particular subset of data or aggregate data across many tables, and do more manipulations on the subset. However, one might ask, it sounds like doing a subquery(See Fig 1). Well, it is true, but CTE can be encapsulated and have multiple references to it, and unlike subquery, you have choices of using CTE as non-recursive just like subquery or recursive. Best of all, it is more maintainable than subquery.
Fig 1: An example of subquery
SELECT Ord.SalesOrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice) FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord
When using CTE, complex SQL statement can be made to easier to understand format that you or other developer can still follow the logic of the what it is intended to do. From years of experience, you know that your requirement is gradually more complex and in this way, the query would be better maintainable.
Temporary Table vs CTE
In a way, CTE is similar to temporary table that it can be logical separated. However, CTE is a temporary resultset that does not physically created in the Tempdb database like the temporary table does. So if exists in memory and cannot be indexed. In addition, table variable is also like the temporary table which is created in the Tempdb database.
Here is a simple example of CTE, where CTEReports is the name of your temporary resultset
;With CTEReports(EmpID, MgrID) AS
(
Select EmployeeID, ManagerID
From Employees
Where ManagerID is NULL
)
Select * from CTEReports
Please note that you should always place a ";" semicolon in front of "With" statement as a good habit.
Simple Example of Recursive CTE
There are two blocks of code within the With statement called the Anchor block and Recursive block.
;WITH Numbers AS
(
SELECT n = 1 <------- first block
UNION ALL
SELECT n + 1 <------- begins of second block
FROM Numbers
WHERE n+1 <= 10
)
select * from Numbers
The anchor block is what defined the temporary resultset, and the recursive block is the one using the resultset.
So first block has 1, and in the second block it increases by +1, and loop until it is less than or equals to 10.
Using MAXRECURSION value to prevent CTE going into infinite loop since you can set your recursion limit.
So in the last select statement, you can write:
Select * From Numbers OPTION (MAXRECURSION 8) when you want to throw an error when recursion goes beyond 8th recursion.
There are many examples on the web about this topic but I hope I have used the simplest examples to explain this useful tool.
So in the last select statement, you can write:
Select * From Numbers OPTION (MAXRECURSION 8) when you want to throw an error when recursion goes beyond 8th recursion.
There are many examples on the web about this topic but I hope I have used the simplest examples to explain this useful tool.
RSS Feed
