New enhancements in T-SQL in Microsoft SQL Server

By Vishal Khanna

SQL Server 2005 provides new features in the T-SQL Language by addition of the following enhancements:

• New large object and XML data types
• Data definition language triggers
• Enhanced language capabilities, Support for recursive queries, better error handling
• Catalog views for access of metadat

The XML Data Type supports the storage of XML documents & fragments inside the database. Further, the Large Value Data Types like varchar(max), nvarchar(max), varbinary(max) are an extension of the native varchar, nvarchar and varbinary data types upto 2³¹ bytes of data.

XML Data Type

This data type may be used to store XML documents and XML fragments inside the database in the form of a column, variable, parameter or function return type. A schema may be associated with an XML data type for the validation of each type. The XML instance is typed. An XML instance without a schema is said to be untyped. Manipulation of data types is possible using XQuery and XML DML. The columns o XML data type may be indexed.

Large Value Data Type

In the native SQL 2000, the data types could store data as below:

varchar - Non Unicode data which could vary in length, upto maximum of 8000 bytes

nvarchar - Unicode data which could vary in length, upto maximum of 4000 bytes.

varbinary - Variable length binary data allowing maximum length of 8000 bytes.

SQL 2005 onwards, these native data types have been power packed to increase their size by increasing their storage capabilities upto 2³¹ bytes (for varchar(max) and varbinary(max); upto 2³⁰ bytes for nvarchar(max). 

The old data types text, ntext, image do exist, but their usage surely will decrease due to extended capabilities of the new Large value data types.

Large value data types work similar to the native data types. They support the cursor FETCH statement, updates using the .Write clause, trigger references in inserted and deleted tables, built in string functions (like SUBSTR and LEN). Large value data types may also be used as variables in batches and scripts.

Language enhancements in T-SQL

Enhanced TOP clause - supports usage of an expression to specify the number of rows or percent of rows returned in the result set.

The new TABLESAMPLE clause may be used to return a random sample of rows from the result set.

Common Table Expressions (CTE) - allow the creation of a temporary named result set from a query, which simplifies creation and usage of recursive queries.

The new OUTPUT clause allows return of a result set that contains the rows affected by a DML command like Insert, Update or Delete.

Introduction of new operators like SOME, ANY, ALL - these may be used for comparing the values in a column with a scalar value.

Support for structured exception handling, allowing usage of the TRY... CATCH block.

New rank functions have been added like ROW_NUMBER(), DENSE_RANK(), NTILE().

The new EXECUTE AS clause allows the user execution context of T-SQL statements.

The APPLY operator has been introduced, that allows invocation of table valued functions for each row in a result set.

Turning of unique values in column rows into multiple columns in a result set may be done using the new PIVOT operator. The UNPIVOT operator may be used to convert multiple columns in a result set into rows.

Thats a full pack of new features in SQL 2005. Watchout for my next on SQL 2008 features.

Cheers!