Using a CTE in SQL Server2005 database queries

universal table Expression (CTEs) is a new feature of SQL Server 2005. They are similar to the alias (as in the Select t1.* from MyTable T1), but are more powerful. In essence, a CTE is a temporary result set that exists only in the statement in which it occurs. You can create a CTE in the Select, INSERT, DELETE, update, or cteate view statements. A CTE is similar to a derived table, but has several advantages.

The advantages of a CTE

Unlike derived tables, a CTE can refer to itself. If you do not have to store the view, you can replace it with a CTE. In a statement, you can also refer to a CTE multiple times. By applying a CTE, you can group the results with a derived column.

Before, I have written about the atomic and molecular inquiry of the article. Atomic queries build a table, and molecular queries are built on atomic queries, providing clarity and reuse. Applying a CTE can also achieve the same goal. You can split the query area into readable "blocks" and then use these blocks to create a complex query. Executing a recursive query is the most important and powerful function of a CTE.

Establish a CTE

The CTE is created by using the keyword with the template:

WITH CTE_name[ (column_name [,...n] ) ]
AS
( CTE_query_specification )

If the names of the columns mentioned in the CTE definition are unique, you may not have to name them. However, you can also rename them.

The following example applies to the AdventureWorks sample database in SQL Server 2005. This database is highly normalized, so several connections are required to assemble employee-related information. The view simplifies this, but it also collects all the information about employees, and you may need only a subset of the information.

AdventureWorks's employee data is distributed across several tables; The problem is compounded by the fact that employees and managers are stored in the same table (HumanResources.Employee), and their names (and other data) are stored in the Person.Contact table.

First, we create a CTE that restores the employee's name.

WITH cte_Employee
AS
(
SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName,
e.Title AS JobTitle, c.Phone, e.ManagerID
FROM HumanResources.Employee AS e
INNER JOIN Person.ContactAS c ON c.ContactID = e.ContactID
)

We can then select a column or a few columns from the CTE, as if it were a standard table or view.

Then we'll go further. We need the names of our employees and their managers, so we use a CTE two times to connect it to ourselves. Here is the complete query code:

WITH cte_Employee
AS
(
SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName,
e.Title AS JobTitle, c.Phone, e.ManagerID
FROM HumanResources.Employee AS e
INNER JOIN Person.ContactAS c ON c.ContactID = e.ContactID
)
SELECT E.FirstName + ' ' E.LastName Employee,
M.FirstName + ' ' M.LastName Manager
FROM cte_Employee AS E
LEFT OUTER JOIN cte_Employee AS M
ON E.ManagerID = M.EmployeeID

Limit: Cannot establish two CTE in one statement.

Summarize

A CTE is a powerful and flexible feature of SQL Server 2005. It makes SQL Server more readable, easier to manage, and reduces the complexity of queries. As described above, you can apply a CTE multiple times in one SQL Server statement.