Thursday, October 25, 2007

Common Table Expressions in SQL Server 2005

I initially wrote this article for www.adminprep.com but apparently, I myself am having difficulty trying to access the site every now and then so I decided to mirror the content in my blog.

Have you ever wished that Microsoft created a feature in SQL Server 2000 that combines the capabilities of views and derived tables? How about writing recursive queries in T-SQL that does not need to be very taxing? With SQL Server 2005 Common Table Expressions, you can do both with ease. In their simple form, you can think of CTEs as an improved version of derived tables that more closely resemble a non-persistent type of view. Look at CTEs as your derived tables in SQL Server 2000. A CTE can be used in many of the same ways you use a derived table. CTEs can also contain references to themselves. This allows the developer to write complex queries simpler, like recursive queries. CTEs can also be used in place of views. The use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work with derived tables normally requires a separate definition for the derived data such as a temporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies.

To understand what a CTE is all about, let’s first take a look at the syntax to create it in SQL Server 2005.

Syntax

In general form a CTE has the following syntax:

WITH cte_alias(column_aliases)
AS
(
cte_query
)
SELECT *
FROM cte_alias


You provide the CTE with an alias and an optional list of aliases for its result columns following the keyword WITH which usually defines the derived table based on the query definition; write the body of the CTE; and refer to it from the outer query.

To put this in the right perspective, let’s come up with a very simple example. Using the Northwind database, we want to display the employee details along with a column that displays the number of orders that the employee has written. This is done by creating a table on the fly that summarizes this information from the orders table. We then join with this table in the INNER JOIN clause based on employee ID.

WITH Count_Orders(employee_ID, orderCount)
AS
(
SELECT employeeID, COUNT(OrderID)
FROM Orders
GROUP BY employeeID
)
SELECT employeeID, Firstname, Lastname, orderCount
FROM Employees E INNER JOIN Count_Orders
ON Count_Orders.employee_ID = E.employeeID


Now, a lot of people might say that this is quite simple. You can do this in SQL Server 2000 using the concept of derived tables or temporary tables. If you were to write the same query in SQL Server 2000, this is how it would look like.

SELECT EmployeeID, Firstname, Lastname, orderCount
FROM Employees E INNER JOIN
(
SELECT employeeID, COUNT(OrderID)
FROM Orders
GROUP BY employeeID
)
AS Count_Orders (employee_ID, order_Count)
ON Count_Orders.employee_ID = E.emplyeeID


One might argue that these two don’t have that much of a difference. Now, imagine that you need to refer to the same derived table within the query. You would have to repeat the same definition, create an alias for it before you can use it again. As you increase the number of references, your code becomes pretty long but repetitive. With CTEs, you no longer have to do these things again and your code becomes easier to read. You can define multiple CTEs and incrementally build on the earlier CTEs or define new results that are then used later on.

Recursive Queries

Whenever a CTE refers to itself, it is considered to be a recursive query. Recursive CTEs are constructed from at least two queries. One is a non-recursive query, which is also referred to as the anchor member. The other is the recursive query, also referred to as the recursive member. These queries are separated by the UNION ALL operator.

Let’s take a look at a simplified generic form of a recursive CTE.

WITH RecursiveCTE(column_list)
AS
(
-- Anchor Member:
-- SELECT query that does not refer to RecursiveCTE

SELECT ...
FROM some_table(s)_or_view(s)
...
UNION ALL
-- Recursive Member
-- SELECT query that refers to RecursiveCTE

SELECT ...
FROM some_table(s)_or_view(s)
JOIN RecursiveCTE
...
)
-- Outer Query
SELECT ...
FROM RecursiveCTE
...


Let’s extend the example we used to demonstrate how to write recursive CTEs. Looking at the Employees table in the Northwind database, we see that a particular employee reports to another employee. One question we can come up with is, “Who reports to whom?” The Employees table of the Northwind database is designed in such a way that the ReportsTo column is a foreign key field that refers to the primary key field EmployeeID. Thus, we can create a query to answer our question. A sample query using CTE will look something like this.

WITH Managers AS
(
SELECT EmployeeID, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
SELECT e.employeeID, e.ReportsTo
FROM Employees e INNER JOIN Managers m ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers


The sample query contains the elements that a recursive CTE must contain. What’s more is that the code is a lot more readable. To create a similar yet non-recursive query that produces the same result in SQL Server 2000, you might come up with something similar to this code:

DECLARE @rowsAdded int
--table variable to hold accumulated results
DECLARE @managers table
(EmpID int, MgrID int, processed int default(0))

--initialize @managers who do not have managers
INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees
WHERE ReportsTo IS NULL

SET @rowsAdded=@@rowcount

--do this while new employees are added in the previous iteration
WHILE @rowsAdded > 0
BEGIN
--mark employee records going to be found in this iteration with
--processed=1

UPDATE @managers SET processed=1 WHERE processed=0

--insert employees who report to employees not yet processed
INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees e
INNER JOIN @managers r ON e.ReportsTo = r.EmpID
WHERE ReportsTo <> EmployeeID and r.processed = 1

SET @rowsAdded = @@rowcount

--mark employee records found in this iteration as processed
UPDATE @managers SET processed=2 WHERE processed=1
END

SELECT * FROM @managers


The first thing you will notice is the codes were quite few when using CTEs compared to the usual T-SQL query you will have to create in SQL Server 2000. This enables the developers to write complex queries with ease. You can also use a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. You do this by including the MAXRECURSION keyword in the SELECT query referring to the CTE. To use it in the previous example

SELECT * FROM Managers OPTION (MAXRECURSION 4)

Given the example above, hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused. As our data requirements become more complex, we need the proper tools to deal with them and the new generation of T-SQL is just the right tools that we need. In my next article, I will introduce more T-SQL enhancements in SQL Server 2008 which is similar to how to address problems like this using CTEs. SQL Server 2008 has introduced a new data type called heirarchyid which I will cover soon

No comments:

Google