Section 3.1. New Data Types

3.1. 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 typesvarchar(max), nvarchar(max), and varbinary(max)extend the storage capacity of varchar, nvarchar, and varbinary data types up to 2³¹ bytes of data.

3.1.1. 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 typethe 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.

3.1.2. Large Value Data Types

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

varchar

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

nvarchar

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

varbinary

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

SQL Server 2005 introduces large value data typesvarchar(max), nvarchar(max), and varbinary(max). You use the max specifier to extend the storage capability of varchar, nvarchar, and varbinary data types to 2³¹ bytes in the varchar and varbinary data types and to 2³⁰ bytes of Unicode data in the nvarchar data type.

In earlier versions of SQL Server, you specified these types as varchar(n), nvarchar(n), and varbinary(n), where n is an integer specifying the in-row storage limit for large character, Unicode, and binary dataup to 8000 bytes for varchar and varbinary and 4000 bytes for nvarchar. If you needed to store more data, you had to use the large object (LOB) data typestext, ntext, and imagewith reduced functionality.

In SQL Server 2005, use varchar(max), nvarchar(max), and varbinary(max) instead of the text, ntext, and image data types, which are slated to be deprecated in a future version of SQL Server.

Large value data types behave in the same way as their smaller counterparts. They are supported for the cursor FETCH statement, for chunked updates through the .WRITE clause, after trigger references in inserted and deleted tables, and with built-in string functions such as LEN and SUBSTR. Large value data types also do not suffer from some of the restrictions of LOB typesthey can be used as variables in batches and scripts, for example.

Use the .WRITE clause in an UPDATE statement to modify part of the value stored in a varchar(max), nvarchar(max), or varbinary(max) column in a table or a view. The .WRITE clause syntax is as follows:

      .WRITE (expression, @Offset, @Length) 

The .WRITE clause replaces a section of the value in a large value data type column starting at @Offset for @Length units with the value expression.

The following example demonstrates the .WRITE clause. First create a table with a single varchar(max) column and add a row to it using the following statement:

     USE ProgrammingSqlServer2005     CREATE TABLE WriteMethodDemoTable(       ID int,       varcharMaxCol varchar(max)     )     INSERT INTO WriteMethodDemoTable (ID, varcharMaxCol)       VALUES (1, 'Imagine this is a very long non-Unicode string.')     INSERT INTO WriteMethodDemoTable (ID, varcharMaxCol)       VALUES (2, 'Imagine this is another very long non-Unicode string.') 

Next, query the table, execute the .WRITE clause, and requery the table using the following statement:

     SELECT * FROM WriteMethodDemoTable     UPDATE WriteMethodDemoTable     SET varcharMaxCol .WRITE('n incredibly', 17, 5)     WHERE ID = 1     SELECT * FROM WriteMethodDemoTable 

Results are shown in Figure 3-1.

Figure 3-1. Results from .WRITE clause example

For more information about the .WRITE clause, see Microsoft SQL Server 2005 Books Online.