What are the new t-sql features sql server 2005?

Question

Where do I find a complete list of new T-SQL features in sql server 2005 comparing with 2000?

A few ones that I know: Pivot, Output, Try..Catch. Anything else? Thank you.

Answer 1

Probably the biggest new feature in sql2005 for my team has been the ability to mirror databases. I'm a huge fan of that. Not sure of the tsql for it, though, so i'm not really helping you am i?

Answer 2

Here is a Microsoft reference from Books online:
http://msdn.microsoft.com/en-us/library/ms189465(SQL.90).aspx

It contains Sample database enhancements, tsql data types and some others. Not the best resource (silly because it's written for SQL Server), but something.

Here is a good resource at SQLServerCentral.com. Unfortunately you need to join to see the information. I have been a member for years as there are a lot of good SQL Server articles on the site. http://www.sqlservercentral.com/articles/Development/2738/
I would copy some of the information over, but I don't think they would be very happy about that.

Answer 3

There is also T-SQL support for Encryption and Key management (which is useful is you want to write functions or stored procedures to encrypt data).

There's a good article here which shows how to use T-SQL to create keys for encryption. There's a another similar article on MSDN Blogs located here.

The Books Online documentation for Creating keys (and other T-SQL support for encryption) can be found here.

Answer 4

My top favourites are:

• VARCHAR(MAX)
• ROW_NUMBER()
• OVER / PARTITION BY (used with ROW_NUMBER() or any aggregate like SUM, COUNT)
• INCLUDED columns in indices
• OUTPUT clause
• TOP on UPDATE & DELETES
• CTE's

Actually.. there's a shitload of good stuff :-)