What's new in SQL Server 2005 -TSql

SQL Server 2005 Changes to T-SQL
------------------------------------

1)Improved Error Handling
T-SQL now supports TRY-CATCH blocks for modern error handling
e.g.
BEGIN TRY
-- SQL statements
END TRY
BEGIN CATCH
-- SQL statements
END CATCH

TRY/CATCH blocks can also be nested.

2) DDL Triggers
You can set up triggers that fire when CREATE, ALTER, or DROP statements are executed.

An example of a DDL trigger that prevents the dropping of a table is as shown:

CREATE TRIGGER OnDropTable ON DATABASE FOR DROP_TABLEAS
RAISERROR ('No drops allowed', 10, 1)
ROLLBACK

3)Top Operator enhancements
Improved paging with parameterized TOP Operator
e.g.
DECLARE @NO_OF_ROWS
BIGINT
SET @NO_OF_ROWS = 10
SELECT TOP (@NO_OF_ROWS) * FROM [Orders] ORDER BY [OrderDate] DESC

Purge the oldest 10,000 rows from TempTable.
DELETE TOP (10000) FROM [TempTable] ORDER BY [TimeStamp]

4) DML with Output
SQL Server 2005 introduces the OUTPUT clause that returns inserted, updated, or deleted rows as part of a DML operation. The OUPUT clause can be used with INSERT, DELETE, and UPDATE statements.

The "DELETED" and "INSERTED" pseudo-tables can be used to get the pre- and post-operation values.
e.g.
UPDATE [Jobs]SET [Status] = 'Done'OUTPUT DELETED.*, INSERTED.*WHERE [Status] = 'In progress'

The DELETED values are not available for INSERT operations while the INSERTED values are not available for the DELETE operations. Both the INSERTED and the DELETED values are available for UPDATE operations

5) New data type
You can store XML data natively in its own column type now.
It supports XQuery for querying against XML data, including extensions that allow XML DDL operations.

The XML data type is a first-class data type in SQL Server 2005 and can be used to define columns, variables, and parameters for stored procedures and user-defined functions. The XML data type can be optionally constrained by an XML schema. SQL Server 2005 also provides built-in XQuery support to query XML data.

The VARCHAR (MAX), NVARCHAR (MAX), and VARBINARY (MAX) data types can store up to 2GB of data and provide alternatives to the existing TEXT, NTEXT, and IMAGE data types respectively.