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

Source: Internet
Author: User
Tags microsoft sql server 2005

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.

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)


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

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.