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:
if exists (select * from sysobjects where id = object_id (' [asdf] ') and OBJECTPROPERTY (ID, ' isusertable ') = 1) drop T ABLE [Asdf]create TABLE [ASDF] ([inttest] [int] IDENTITY (1, 1) not NULL, [text] [text] null, [Varcharmax] varchar (MA x) 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 I Dentity_insert [ASDF] off Run 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 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 Ms SQL2005 and above, add a large value data type (varchar (max), nvarchar (max), varbinary ( Max)). 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. MicrosoftThis data type is used instead of the previous text, ntext, and image data types, and the corresponding relationship between them is: varchar (max)-------Text;nvarchar (max)-----ntext;varbinary (max ----image. has a large value data type, it is much more flexible to operate on 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.
Differences between text and varchar (max) data types in SQL Server
