Editor's note:
The features described here aren't final, nor are they guaranteed to be in the final product release in the same form or at all.
You've probably heard about the new ability in the upcoming release of SQL Server, code-named Yukon, to develop programmable objects through Common Language Runtime (CLR)-based languages such as Visual Basic .NET and C#. (Randy Holloway and Andrej Kyselica cover this much-anticipated CLR integration in "Ready or Not, Here Comes the CLR," page 22.) You might have thought that as a result, Microsoft would put less effort into enhancing T-SQL, but that's far from the case. CLR-based programming complements T-SQL in its weak areas. T-SQL has always been strong in data-manipulation and set-based operations and still has no match in these areas. However, CLR languages are stronger in areas that involve complex algorithms, iterative processing, and so on. T-SQL has a long life ahead of it, as the effort that Microsoft put into these T-SQL enhancements shows.
Microsoft has improved many aspects of T-SQL in Yukon, providing long-awaited responses to programmers' needs and requests. Relational enhancements in Yukon Beta 1 include Common Table Expressions (CTEs) that have recursive querying capabilities, PIVOT and UNPIVOT operators, a more functional TOP clause, and CROSS APPLY and OUTER APPLY operators. INSERT, UPDATE, and DELETE statements can now return output, and the WAITFOR command lets you wait for a Data Manipulation Language (DML) action to affect at least one row or wait for a notification to arrive at a queue before control passes to the next command. Triggers now let you trap Data Definition Language (DDL) events such as dropping an object, and you can even get notifications when DML events take place. A new error-handling mechanism based on Try/Catch blocks lets you manage errors much more effectively than before. Important data-type enhancements include a new XML data type, separate date and time data types (finally!), and the MAX option, which allows much more elegant manipulation of large objects in dynamic columns such as varchar, nvarchar, and varbinary. And finally, a new rowset provider called BULK lets you access files more easily than ever. (A rowset provider lets you access a data source in relational form, meaning that you get a table as a result of a query against the provider.)
I can't go into great technical depth or specify syntax details in this preview article, but I can give you a taste and provide highlights of the new features. If you're enrolled in the Yukon beta-testing program, you can find more details in the accompanying Books Online (BOL) and in the "Yukon T-SQL Enhancements" white paper available with the Yukon Beta Readiness Kit. In future articles closer to the release of the product, I'll go into more implementation details.
Common Table Expressions
Have you ever wanted to combine the capabilities of views and derived tables? Need to write recursive queries in T-SQL? With CTEs, you can do both! A CTE is a named table expression followed by a query. CTEs come in two forms: nonrecursive and recursive. A nonrecursive CTE blends the characteristics of derived tables and views. As with a derived table, you have a query to which you give an alias, potentially also providing aliases to the result columns, and the CTE doesn't persist after the outer query finishes. As with a view, you can refer to the table expression more than once in the outer query. Typically, you'd use a nonrecursive CTE when you need to refer to a table expression more than once and you don't want it to persist in the database.
For example, suppose that for each year, you want the total number of orders for that year and the previous year from the Northwind database's Orders table. The code in Listing 1 shows how you can get this result by using a CTE. Following the WITH keyword are the CTE's alias—YearlyOrders—and the result column aliases, as callout A shows. The body (at callout B) holds the query you provided an alias for, which returns the count of yearly orders. The outer query performs an outer join between two instances of YearlyOrders—CurYear and PrevYear—matching current and previous yearly orders. Figure 1 shows the result of running Listing 1's code.
To achieve the same result in pre-Yukon releases of SQL Server, you have two options. One option is to create a view and refer to it twice in the query, a method that forces you to create a persistent object in the database. The other option is to use two derived tables, which forces you to duplicate code.