Chapter 3. T-SQL Enhancements

SQL Server 2005 extends the T-SQL language in several significant ways:

  • New XML and large object data types

  • New and enhanced language capabilities, including support for recursive queries and improved error handling

  • Data Definition Language (DDL) triggers

  • Catalog views to access metadata

This chapter discusses these enhancements and changes.

New Data Types

SQL Server 2005 introduces the xml data type and large value data types. The xml data type supports storing XML documents and fragments in the database. Large value data types—varchar(max), nvarchar(max), and varbinary(max)—extend the storage capacity of varchar, nvarchar, and varbinary data types up to 231 bytes of data.

The xml Data Type

The built-in xml data type stores XML documents and fragments natively as a column, variable, parameter, or function return type. A schema can be associated with an xml data type to validate each instance of the type—the XML instance is then typed. An XML instance without a schema is untyped. xml data types can be manipulated using XQuery and XML DML. Columns of xml data type can be indexed.

Chapter 7 provides an in-depth look at support for XML data in SQL Server 2005.

Large Value Data Types

SQL Server 2000 has varchar, nvarchar, and varbinary variable-length data types:


Variable-length non-Unicode data with a maximum length of 8,000 bytes


Variable-length Unicode data with a maximum length of 4,000 bytes


Variable-length binary data with a maximum ...

Get Programming SQL Server 2005 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.