ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These new functions allow you to efficiently analyze data and provide ranking values to result rows of a query.
. Common table expressions—A common table expression (CTE) is a temporary named result set that can be referred to within a query, similarly to a temporary table. CTEs can be thought of as an improved version of derived tables that more closely resemble a non-persistent type of view. You can also use CTEs to develop recursive queries that you can use to expand a hierarchy.
. PIVOT/UNPIVOT operator—The PIVOT operator allows you to generate crosstab reports for open-schema and other scenarios in which you rotate rows into columns, possibly calculating aggregations along the way and presenting the data in a useful form. The UNPIVOT operator allows you to normalize pre-pivoted data.
. APPLY—The APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression.
. TOP enhancements—In SQL Server 2005, the TOP operator has been enhanced, and it now allows you to specify a numeric expression to return the number or percentage of rows to be affected by your query; you can optionally use variables and subqueries. You can also now use the TOP option in DELETE, UPDATE, and INSERT queries.
. DML with results—SQL Server 2005 introduces a new OUTPUT clause that allows you to return data from a modification statement (INSERT, UPDATE, or DELETE) to the processing application or into a table or table variable.
. Exception handling for transactions—Earlier versions of SQL Server required you to include error-handling code after every statement that you suspected could potentially generate an error. SQL Server 2005 addresses this by introducing a simple but powerful exception-handling mechanism in the form of a TRY...CATCH T-SQL construct.