SQL Server 2005: What's New? - PIVOT and UNPIVIT Operators

(Page 2 of 5 )

PIVOT and UNPIVOT are relational operators that, like CTEs, are geared toward BI applications. To put it briefly, PIVOT provides the basic operation required for crosstabs, effectively converting columns into rows. UNPIVOT does the reverse, converting rows into columns. This allows you to perform quick crosstabs without having to use OLAP. This is a great feature for report building!

APPLY, CROSS APPLY and OUTER APPLY Operators

APPLY is a relational operator that allows you to use a table-valued function once for each row of outer table expressions. You use APPLY in the FROM clause much like a JOIN, and it comes in two forms - CROSS APPLY and OUTER APPLY. CROSS APPLY invokes a table-valued function for each row in an outer table expression. OUTER APPLY is similar but it will return rows from the outer table where the table-valued function returned an empty set. This is a reasonably useful feature, but not life changing for most SQL programmers.

Exception Handling in Transactions

In an effort to render the GOTO statements of previous TSQL implementations useless, SQL Server 2005 implements TRY/CATCH blocks. These blocks work as expected, just like blocks of the same type in C# or VB.NET. This is a good feature for multipart transactions and will definitely demonstrate increased value for developers.

The new features in TSQL offer a lot of benefit for SQL programmers and definitely warrant poking around with the server. Next up is the expanded XML support provided by SQL Server 2005. There is now an XML data type with methods built to operate on that data as well as support for XQuery and DML Extensions.

Next: XML Data Type >>
 

More MS SQL Server Articles
More By David Fells

Comments On: SQL Server 2005: What's New?

· That's simple, with XML support included you can extend you simple relational...    · In one sentence you yell that not every solution is right for every problem. Next...   >>> Post your comment now!