Summary

This chapter highlights the new and improved T-SQL language features in SQL Server 2005. It begins with a comparison of T-SQL with SQLCLR to help you decide where to use which option. T-SQL is still the primary language that DBAs and developers should use for any kind of data access and scripting.

The rest of the chapter discusses the T-SQL enhancements in SQL Server 2005. SQL Server 2005 has enhanced the TOP operator and introduces a new TABLESAMPLE keyword that you can use to restrict the result set. The chapter next talks about a very cool T-SQL feature, CTE, which can be used instead of derived tables to produce simpler and efficient queries. The real power of CTEs can be seen when they are used recursively.

The other features discussed in this chapter include the max specifier to store 2GB of character or binary data in varchar, nvarchar, or varbinary columns; structural error handling using trY...CATCH blocks; DDL triggers to handle database- and server-level DDL events; event notifications to asynchronously handled database, server, and SQL trace events; the new snapshot isolation level to reduce locking and increase concurrency and scalability; analytical ranking functions; new relational operators, including PIVOT, UNPIVOT, and APPLY; the OUTPUT clause to capture changes made by DML statements; enhancements to the OPENROWSET function to bulk load data; and metadata views, index enhancements, and the XML showplan.

Chapter 7, "SQL Server 2005 Security," covers the security-related features in SQL Server 2005.