SQL Server 2005: What Makes the Difference? Part 5: T-SQL

There is enormous number of enhancements in the Transact-SQL language in SQL Server 2005. I already mentioned all of the new and improved language elements for handling the XML data. You can use string functions for large objects if you use new varchar(max), nvarchar(max) or varbinary(max) data types.

Error handling is finally similar to error handling in .NET and other programming languages with TRY … CATCH block. DDL triggers and event notifications enable you to audit and even rollback DDL events. The OUTPUT clause in the DML statements gives you a peek into the data before and after a modification (the inserted and deleted tables) without a DML trigger. The APPLY operator invokes a table-valued function for each row returned of the outer table, thus giving you a kind of row-based JOIN. For many years, a common problem was how to get a sequential number together with each row of the returned row set; you can now solve this and many similar row-oriented problems with ranking functions (ROW_NUMBER, RANK, DENSE_RANK and NTILE). New PIVOT and UNPIVOT operators were longed for a long time as well. Although they are here now, I find them implemented halfway only; they are not dynamic yet. I am going to stop the enumeration here; I want to mention only one novelty more, but the one that impresses me the most – the Common Table Expressions (CTEs).

A CTE is a temporary result set declared with a subquery in the new WITH clause of the SELECT statement.  You could use subqueries already in previous versions of the T_SQL language in the SELECT, FROM and WHERE clauses. A CTE is a somewhat more useful derived table (a derived table is a subquery in the FROM clause): it can return multiple rows and multiple columns, but compared to a derived table you can use it many times in the same query, and thus make the query more efficient. The following table compares the subqueries in the different clauses of the SELECT statement:

Besides better performance, CTEs finally resolve a well-known problem: expanding hierarchies (organizational charts, bills of material …). A CTE can reference itself and thereby create a recursive query. A recursive CTE not only simplifies the code, but also improves the performance compared to other known T-SQL solutions.

As I mentioned, CTEs are my number one in the T_SQL enhancements. Now, for the end of this blog, here is a little advertisement:

For more on Transact-SQL queries for SQL Server 2005, read “Inside Microsoft^® SQL Server^™ 2005: T-SQL Querying” by Itzik Ben-Gan, Lubor Kollar, and Dejan Sarka, ISBN 0-7356-2313-9.