|
|
Posted on 6/13/2018 11:07:11 AM
|
|
|
First, varchar(max) is a substitute for the text data type used by Microsoft,Older field types such as text may be canceled in subsequent versions。 So what text can be saved, varchar( max) can definitely be saved. In fact, the number of characters it can store is very large. The 420 million characters that can be stored are hard to reach, which I believe is hard to reach.
I only knew beforetext and image are data types that may be obsolete by SQL Server, but the specific reason is not clear, when I read today, I found the difference between text and varchar(max) and nvarchar(max), mainly because of 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 "Text" column is text, then it cannot be used for operations such as "=" and "left()", such as the following example:
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] IDENTITY (1, 1) 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
Run the query:
Enquiry 1:
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 1
The data types text and varchar are not compatible in the equal to operator.
Enquiry 2:
SELECT [text]
,[varcharmax]
FROM [testDB]. [dbo]. [asdf]
where
[varcharmax] = '1111111'
can run successfully
In MS SQL2005 and above, add large value data types (varchar(max), nvarchar(max), varbinary(max)). The large value data type can store up to 2^30-1 byte of data.
These data types are behaviorally identical to the smaller data types varchar, nvarchar, and varbinary.
Microsoft's claim is to replace the previous text, ntext, and image data types with this data type, 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 work with large value data than before. For example, text cannot be used 'like' before, but after varchar(max), there are no these problems, because varchar(max) is the same as varchar(n) in behavior, so what can be used in varcahr can be used on varchar(max).
In addition, this supports the use of AFTER triggers on large value data type column references in inserted and deleted tables. text is not good, in short, after using the large value data type, I am "my waist no longer hurts, my legs are not sore, and I can go up to the sixth floor in one breath". What are you waiting for, use the big value type.
|
Previous:Uh-huh, eh-huh, eh-huhNext:ef (lambda expression) queries several exceptions encountered in the data
|
