Varchar (max), nvarchar (max), and varbinary (max) Data Types

Microsoft SQL Server 2005 introducesMaxDescription. This specifier is enhancedVarchar,NvarcharAndVarbinaryData storage capability.Varchar (max),Nvarchar (max)AndVarbinary (max)It is collectively referred to as the big value data type. You can use the bytes data type to store data up to 2 ^ 31-1 bytes.

Note:
WhenSp_tableoptionStored Procedure'Large value types out of row'When the option is set to off, the bytes type in-Row Storage is limited to 8000 bytes. When this option is set to on, only the 16-byte root is stored in the row. For more information, see sp_tableoption (TRANSACT-SQL ).

 

The behavior of the shard data type and the corresponding small data typeVarchar,NvarcharAndVarbinarySimilar. This similarity enables SQL Server to more efficiently store and retrieve large characters, Unicode, and binary data.

With the volume data type, SQL Server is used in earlier versions of SQL ServerText,NtextAndImageData types are not possible. For example, in SQL Server 2005, you can define variables that can store a large amount of data (up to 2 ^ 31 bytes of characters, integers, and Unicode data. For more information, see Transact-SQL variables.

The following table describes the relationship between the volume data type and the corresponding data type in earlier versions of SQL Server.

Shard Data Type	Lob in earlier versions
Varchar (max)	Text *
Nvarchar (max)	Ntext *
Varbinary (max)	Image

* SQL Server 6.5 clients do not supportNtextData type, so it cannot be identifiedNvarchar (max).

Important:
Please useVarchar (max),Nvarchar (max)AndVarbinary (max)Data Type, insteadText,NtextAndImageData type.

 

The behavior of the shard data type and the corresponding small data typeVarchar (N),Nvarchar (N)AndVarbinary (N)Same. The following describes how to use the sequence data type in certain situations:

• Cursor
  Because you can define a struct data type variable, you can assign the data in the struct data type column returned by fetch to the local variable. For more information, see fetch (TRANSACT-SQL ).
  The use of the cursor data type does not affect the forced Implementation of the cursor type conversion usage.
  
• Block update
  The update statement is currently supported..The write () clause partially updates the basic values data column. This is similar toText,Ntext,ImageData-type text pointer operations, writetext, and updatetex. For more information, see Update (TRANSACT-SQL ).
  
• Trigger
  SupportedInsertedAndDeletedThe After trigger is used for reference of the values data type column in the table. For more information, see create trigger (TRANSACT-SQL ).
  
• String Functions
  The built-in string functions for operable characters and binary data are enhanced to support parameters of the bytes data type. These functions include:
  
  • Col_length
    
  • Charindex
    
  • Patindex
    
  • Len
    
  • Datalength
    
  • Substring