Differences between text and varchar (max) data types in SQL Server

Differences between text and varchar (max) data types in SQL Server

It was only known that text and image were data types that could be retired by SQL Server, but it was not clear why the difference between text and varchar (max) and nvarchar (max) was found in reading today, mainly the restriction of operators. Text can only be used by the following functions:

function	Statement
Datalength	READTEXT
PATINDEX	SET TEXTSIZE
SUBSTRING	UpdateText
TEXTPTR	WriteText
Textvalid

For example, if the data type of the column "text" is text, it will not be used for operations such as "=" "Left ()", such as the following example:

To create a table and populate the data:

if exists (select * from sysobjects where id = object_id (' [asdf] ') and OBJECTPROPERTY (ID, ' isusertable ') = 1)

DROP TABLE [ASDF]

CREATE TABLE [ASDF] (

[Inttest] [INT] The IDENTITY (1, 1) is not NULL,

[Text] [Text] Null

[Varcharmax] varchar (max) NULL)

ALTER TABLE [asdf] with NOCHECK ADD CONSTRAINT [pk_asdf] PRIMARY KEY nonclustered ([inttest])

SET Identity_insert [ASDF] On

INSERT [ASDF] ([inttest], [text], [Varcharmax]) VALUES (1, ' 1111111 ', ' 1111111 ')

SET Identity_insert [ASDF] OFF

To run the query:

Query one:
SELECT [Text]

, [Varcharmax]

from [TestDB]. [dbo]. [ASDF]

where

[Text] = ' 11111 ' and

[Varcharmax] = ' 1111111 '

The following error message appears:

MSG 402, Level 16, State 1, line 1th

The data type text and varchar are incompatible in the equal to operator.

Query two:
SELECT [Text]

, [Varcharmax]

from [TestDB]. [dbo]. [ASDF]

where

[Varcharmax] = ' 1111111 '

Can run successfully

In versions of Ms SQL2005 and above, a large value data type (varchar (max), nvarchar (max), varbinary (max)) is added. A large value data type can store up to 2^30-1 bytes of data.

These data types behave in the same way as the smaller data types varchar,nvarchar , and varbinary .

Microsoft's argument is that this data type is used instead of the previous text,ntext , and image data types, and the correspondence between them is:

varchar (max)-------text;

nvarchar (max)-----ntext;

varbinary (max)----image.

With large-value data types, it is much more flexible to manipulate large-value data than before. For example: Before the text is not "like", with varchar (max) After the problem, because varchar (max) in the Behavior and varchar (n) The same, so can be used in Varcahr can be used in the varchar (max On

In addition, this also supports the use of AFTER triggers on column references to large-value data types in the inserted and deleted tables. Text will not work, in short, with a large value data type, I am "waist also does not ache, leg also not sour, breath can also on six floor." What are you waiting for, use the big value type now.

Original: http://www.cnblogs.com/jhxk/articles/1633578.html Note: This article is only for my future convenience and exists here, if there is a copyright problem, please contact me to delete the article.

Differences between text and varchar (max) data types in SQL Server