TSQL Enhancements on SQL 2005

SQL Server 2005 as "A secure, scalable, Enterprise-class Data Management platform for Windows Server systems"

The features introduced in SQL Server 2005 are aimed at the following categories:
(1) Developer Productivity (.net CLR , Server Brocker, Notification Services)
(2) Business Intelligence
(3) Enterprise Data Management

When to use ,net CLR and when to use TSQL?
Use T-SQL when you are performing set based operations like joins etc use CLR based code when you want to do CPU intensive functions (like complex calculations, iterations etc). Also, if need to take advantage of some framework class features, CLR functions are the best choice.
T-SQL and CLR functions can be interleaved Ex: SELECT dbo.myCLRFunction(), dbo.myTSQLFunction() FROM mytable

CLR procedure call:
Let's say you created a CLR procedure. The first step is to register the procedure in a SQL Server database. When you do this,
SQL Server stores the assembly in binary content in the "sys.assemblies" table and other related tables. Once the assembly has been registered, you will then need to wrap it around a normal SQL procedure or function. The CREATE statements have a new
EXTERNAL NAME clause that lets you do that.Once that happens, when you invoke the procedure and SQL finds out that is CLR based, the CLR runtime is invoked.

TSQL Enhancements on SQL 2005

(1) New Data Types
a) XML, First class data type. Using that variablem table column, parameters can be created.
XQuery: New one, which can be used to manipulate the XML Content.
b) Varchar(max), nvarchar(max), varbinary(max) supports upto 2 GB of data. same character manipulation function for both small and larger data.

(2) MARS (Multiple Active Result Sets) support MARS will allow you to fetch multiple results sets using a single connection. Earlier, you would get an error asking you
to close the active connection.
(3) Native XML support
(4) New Isolation Levels
(5) Exception Management Now Exception handling is more structured like any other .NET language.Its more robust when compared to the conventional @@ERROR statements. More often in TSQL alternate statements used to be @@Error. Welcome to the world of Try - Catch Blocks. Any error that is raised in the TRY block is caught in the CATCH block.
There are some system functions that help you take necessary actions like.,
Error_number – Returns the Error Number encountered Error_Text – Returns the Error Text encountered Error_Severity – Returns the severity number encountered Error_State – Returns the error state number encountered TRY CATCH can be nested to any levels and error raised will be caught at the nearest CATCH block.
Read more on this at: http://www.sswug.org/see/20064
(6) Recursive Queries
(7) New T-SQL functions for ranking and transforming data Rank : provides rank for each row with given partition, SELECT RANK() OVER (PARTITION BY Gender ORDER BY TotalPercent DESC) AS [Rank] FROM dbo.StudentMarks
- One caveat to note in the RANK function is: If there are two rows with the same rank, the next higher rank will skip by 1
Dense_Rank,
NTitleThe NTILE function can be used to distribute the rows in an ordered partition into a specified number of groups
SELECT NTILE(2) OVER (PARTITION BY Gender ORDER BY TotalPercent DESC) AS [TiledOnGender]
--- ROW_NUMBER() would return the running number for each rows
(8) DDL Triggers DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server.
The scope of the trigger depends on the event. For example, a DDL trigger created to fire in response to a CREATE TABLE event will do so whenever a CREATE TABLE event occurs in the database. A DDL trigger created to fire in response to a CREATE
LOGIN event will do so on whenever a CREATE LOGIN event occurs in the server.
DDL triggers can be created to fire in response to : One or more particular DDL statements A pre-defined group of DDL statements.A DDL Trigger can fire after execution of any Transact-SQL event belonging to a pre-defined grouping of similar events.For example, if you want a DDL trigger to fire after any CREATE TABLE, ALTER TABLE, or DROP TABLE statement executes, you
can specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER statement.
Refer to this link for more information: http://www.extremeexperts.com/sql/Yukon/DDLTriggers.aspx

APPLY operator : The table-valued function acts as the right input and the outer table expression acts as the left input
Indexes:No major changes on Indexes and Cursors.
Index operations can now be done online; users can still access the table data and use other indexes on the table while one index is being created, altered or dropped

The MAXDOP clause can now be specified on index data definition language, thus controlling the number of parallel operations used by that specific statement.

Indexes in SQL Server also support XML columns, which is a big improvement

The new ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options can e used to control the level at which locking occurs for the index.