Using Large-Value Data Types
Microsoft SQL Server 2005 introduces the max specifier. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data.
Note
When the sp_tableoption stored procedure 'large value types out of row' option is set to OFF, the in-row storage limit for large-value types is 8,000 bytes. A 16-byte root is stored in-row when this option is set to ON. For more information, see sp_tableoption (Transact-SQL).
The large-value data types are similar in behavior to their smaller counterparts, varchar, nvarchar and varbinary. This similarity enables SQL Server to store and retrieve large character, Unicode, and binary data more efficiently.
With large-value data types you can work with SQL Server in a way that was not possible using the text, ntext and image data types from earlier versions of SQL Server. For example, in SQL Server 2005, you can define variables that can store large amounts of data, up to 2^31 bytes of character, binary, and Unicode data. For more information, see Transact-SQL Variables.
The following table shows the relationship between the large -value data types and their counterparts from earlier versions of SQL Server.
| Large-value data types | LOB from earlier versions |
|---|---|
varchar(max) |
text* |
nvarchar(max) |
ntext* |
varbinary(max) |
image |
* SQL Server version 6.5 clients do not support the ntext data type; therefore, they do not recognize nvarchar(max).
Important
Use varchar(max), nvarchar(max), and varbinary(max) data types instead of text, ntext, and image data types.
Large-value data types exhibit the same behavior as their smaller counterparts, varchar(n), nvarchar(n), varbinary(n). The following describes using large-value data types in some particular scenarios:
- Cursors
Because large-value data type variables can be defined, data from large-value data type columns of a FETCH can be put into local variables. For more information, see FETCH (Transact-SQL).
Using large-value data types does not affect cursor usage that forces cursor type conversion. - Chunked updates
The UPDATE statement now supports a **.**WRITE( ) clause to perform partial updates to the underlying large-value data columns. This is similar to the text pointer operations, WRITETEXT and UPDATETEXT, supported on text, ntext, image data types in earlier versions of SQL Server. For more information, see UPDATE (Transact-SQL). - Triggers
AFTER triggers are allowed on large-value data type column references in the inserted and deleted tables. For more information, see CREATE TRIGGER (Transact-SQL). - String functions
The built-in string functions that can operate on character and binary data are enhanced to support large-value data types as arguments. These functions include the following:- COL_LENGTH
- CHARINDEX
- PATINDEX
- LEN
- DATALENGTH
- SUBSTRING
For information about string functions, see String Functions (Transact-SQL).