T-SQL Enhancements in SQL Server 2005

• By Bob Beauchemin, Niels Berglund, Dan Sullivan
• Jul 30, 2004

This chapter is from the book

First Look at SQL Server 2005 for Developers, A

Sql server 2005 includes new Transact-SQL (T-SQL) functionality. The enhancements span the range from an alternative mechanism for transaction isolation to declarative support for hierarchical queries. And statement-level recompilation even improves existing T-SQL applications that were written before 2005.

Improvements to Transact-SQL

Microsoft has continually improved the Transact SQL language and the infrastructure of SQL Server itself. In brief, the improvements include the following:

SNAPSHOT isolation—Additional isolation level that does not use write locks

• Statement-level recompile—More efficient recompilation of stored procedures

• Event notifications—Integration of Data Definition Language (DDL) and DML operations with Service Broker

• Large data types—New data types that deprecate TEXT and IMAGE

• DDL triggers—Triggers that fire on DDL operations

• Common Table Expressions—Declarative syntax that makes a reusable expression part of a query

• Hierarchical queries—Declarative syntax for tree-based queries

• PIVOT—Declarative syntax aggregations across columns and converting columns to rows

• APPLY—New JOIN syntax made for use with user-defined functions and XML

• TOP—Row count based on an expression

• Transaction abort—TRY/CATCH syntax for handling errors