Large Object Data Type Enhancements

Often when working with large strings in SQL Server 2000, developers were forced to use a text, image, or ntext data type. Most developers quickly realized that working with these large object data types was cumbersome. Some of the limitations with text, image, and ntext data types include are as follows:

• You cannot declare a variable of these types.

• Many string functions, such as LEFT, RIGHT, and so on, do not work with these types.

• These data types often require special handling, by using functions such as TEXTPTR and TEXTVALID and T-SQL statements such as WRITETEXT and UPDATETEXT.

SQL Server 2005 fixes this problem by introducing enhancements to the varchar, varbinary, and nvarchar data types to allow for increased storage. The max option now allows varchar, nvarchar, and varbinary to hold up to 2GB in this release. max may refer to a new maximum in a future release.

The types using the max specifier do not require any special handling, as do text, ntext, and image types. You can treat the max large object types as regular varchar, nvarchar, and varbinary types, and you can use them in joins, in subqueries, to order by, to group by, with distinct clauses, with aggregates, for comparison, for concatenation, and with string functions such as LEFT, RIGHT, SUBSTRING, and so on. You can define variables and parameters of large object types with the max specifier, and you can store up to 2GB of data in the variable or parameter.

Note

Because the max specifier allows storing up to 2GB of data and at the same time offers the flexibility to use the type as traditional varchar, varbinary, or nvarchar types, you might be inclined to use the max specifier for all string or binary columns. However, you should consider the performance implications of I/O and CPU costs involved with rows spanning multiple pages. You should use the max specifier only when large storage (more that 8000 bytes) is desired for the column.

Here is an example of using the max specifier in a table column and a variable declaration:

USE AdventureWorks; GO IF OBJECT_ID('HumanResources.EmployeeNotes') IS NOT NULL    DROP TABLE HumanResources.EmployeeNotes; GO CREATE TABLE HumanResources.EmployeeNotes  (EmployeeID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,   Notes NVARCHAR(MAX)); GO DECLARE @varNotes NVARCHAR(MAX) SET @varNotes = N'New Hire 4/11/2005' INSERT INTO HumanResources.EmployeeNotes SELECT @varNotes; GO SELECT * FROM HumanResources.EmployeeNotes ; GO

This script illustrates declaring a column and a variable of type nvarchar(max).

Often when you store a large amount of data in a max type column, you might want to update just part of the column or variable data instead of completely replacing the value. The legacy large object types (text, ntext, and image) provided the UPDATETEXT T-SQL statement to change only a portion of data. For max types, SQL Server 2005 provides similar functionality via the .write(expression, @offset, @length) clause, which can be used in a SET statement for variables and in an UPDATE statement for a column, as illustrated in the following script:

UPDATE HumanResources.EmployeeNotes SET Notes.write('; Orientation and training complete 4/13/2005', NULL, NULL); SELECT * FROM HumanResources.EmployeeNotes ; GO ------------------ DECLARE @varNotes NVARCHAR(MAX) DECLARE @yearIndex INT SELECT TOP(1) @varNotes = Notes FROM HumanResources.EmployeeNotes; SET @yearIndex = CHARINDEX('2005', @varNotes) SET @varNotes.write('2004', @yearIndex - 1, 4); SET @yearIndex = CHARINDEX('2005', @varNotes) SET @varNotes.write('2004', @yearIndex - 1, 4); PRINT @varNotes; GO

The first batch in this script runs an UPDATE statement and passes @offset and @length as NULL in the .write() clause, which indicates that you want to append the first parameter to the existing value. The second batch in this script illustrates updating parts of the nvarchar(max) variable data by using the .write() clausein this case replacing 2005 with 2004 in the variable string.

Tip

In addition to previously mentioned enhancement to the varchar, varbinary, and nvarchar types, SQL Server 2005 also introduces a new data type, xml, which can be used to store XML documents and fragments in a table column, variable, or parameter. SQL Server provides several methods on this data type that can be used to query and update the XML data. The new xml data type and T-SQL constructs related to this type are discussed in Chapter 10, "XML and Web Services Support in SQL Server 2005."