MSDN Magazine - October 2007 - (Page 38)

Figure 3 Querying with a CTE ;WITH EmpOrdersCTE (EmployeeID, NumOrders, MaxDate) AS ( SELECT EmployeeID, COUNT(*), MAX(OrderDate) FROM Orders GROUP BY EmployeeID ) SELECT e.EmployeeID, oe.NumOrders, oe.MaxDate, e.ReportsTo AS ManagerID, om.NumOrders, om.MaxDate FROM Employees AS e INNER JOIN EmpOrdersCTE oe ON e.EmployeeID = oe.EmployeeID LEFT JOIN EmpOrdersCTE om ON e.ReportsTo = om.EmployeeID ;WITH myCTE (CustID, Co) AS ( SELECT CustomerID, CompanyName FROM Customers ) SELECT CustID, Co FROM myCTE The CustomerID and CompanyName columns are aliased with CustID and Co. Then immediately following the CTE is a SELECT statement that references the CTE using its column aliases. Understanding CTEs Before you design a CTE, you must understand how it works and what rules it follows. This section describes where CTEs can be used as well as what can and cannot be used inside a CTE. For starters, CTEs can be created and used inside of a T-SQL batch, a user-defined function, a stored procedure, a trigger, or a view. A CTE can only be referenced by the statement that immediately follows the CTE. This means that if you want to use a CTE, you must write the query that refers to the CTE immediately after the CTE in the T-SQL batch. For example, the following batch will produce an error: ;WITH myCTE (CustID, Co) AS ( SELECT CustomerID, CompanyName FROM Customers ) SELECT CompanyName FROM Customers WHERE CustomerID = ‘ALFKI’ SELECT CustID, Co FROM myCTE Another option is to create a derived table (also known as an inline view). A derived table can be created by simply moving a SELECT statement inside of a FROM clause surrounded by parenthesis. It can then be queried against or joined to just like a table or view. The code in Figure 2 solves the same query that Figure 1 solves, but instead of using a view it uses derived tables. While derived tables are only accessible within the statement in which they exist, tables generally make a query more difficult to read and thus to maintain. This problem can be multiplied if you want to use the derived table multiple times within the same batch, as you must then copy and paste the derived table to reuse it. A CTE is a nice fit for this type of scenario since it makes the T-SQL much more readable (like a view), yet it can be used more than once in a query that immediately follows in the same batch. Of course, it is not available beyond that scope. Additionally, the CTE is a language-level construct—meaning that SQL Server does not internally create temp or virtual tables. The CTE’s underlying query will be called each time it is referenced in the immediately following query. Therefore, this same scenario can also be written using a CTE, as shown in Figure 3. The EmpOrdersCTE gathers the aggregated data and is then used by the query that immediately follows the CTE. Using the CTE, the code in Figure 3 makes the query very readable (like a view), yet does not create a system object to store the metadata. In this code, myCTE is only available for the first query that follows it. When the second query refers to myCTE, the CTE is not in scope and an exception is thrown (Invalid object name ‘myCTE’). Also note that since a CTE is intended to be referred to by another query, which may then reprocess the data anyway, a CTE’s query cannot contain statements such as ORDER and COMPUTE. However, complex statements such as FOR XML can still be used to define and operate on a CTE. For example, you could query a CTE and return its results using the FOR XML clause, as shown here. ;WITH myCTE AS ( SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID ) SELECT CustomerID, CompanyName, OrderID, OrderDate FROM myCTE FOR XML AUTO Structure of a CTE Now I’ll demonstrate how to construct a CTE using a simple CTE as an example. A CTE begins with the WITH keyword. However, if the CTE is not the first statement in the batch, you must precede the WITH keyword with a semicolon. As a best practice, I prefer to prefix all of my CTEs with a semicolon—I find this consistent approach easier than having to remember whether I need a semicolon or not. The WITH keyword is followed by the name of the CTE, which is then followed by an optional list of column aliases. The column aliases correspond to the columns that are returned by the SELECT statement inside the CTE. Following the optional column aliases is the AS keyword—this is required. And the AS keyword is then followed by the query expression that defines the CTE enclosed within parenthesis. Take a look at this example: 38 msdnmagazine Data Points Once a CTE is defined, it can be referenced multiple times by the first query that follows it. This is especially useful when a query needs to refer to the CTE more than once. The code sample in Figure 3 demonstrates how the EmpOrdersCTE is referred to twice by the query so it can grab the emplo yees and their supervisors. This is very useful when you need to reference the same rowset more than once; it is simpler to refer to the CTE twice than it is to duplicate the query. A CTE does not have to be used by a SELECT statement; it is available for use by any statement that refers to the rowset that the CTE generates. This means a CTE can be followed by a SELECT, INSERT, UPDATE, or DELETE statement that uses the CTE. You can also use forward only and snapshot cursors on queries that use CTEs.

Table of Contents Feed for the Digital Edition of MSDN Magazine - October 2007

Cover Contents Toolbox CLR Inside Out Basic Instincts Data Points Cutting Edge Pooled Threads WPF Threads Parallel Linq Parallel Performance Mobile Apps Test Run Foundations Windows with C++ Netting C++ .NET Matters { End Bracket } Net Nuptials

MSDN Magazine - October 2007

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.