Differences between char, varchar, text, and nchar, nvarchar, ntext in SQL Server

Ext.: http://blog.csdn.net/jackychu/article/details/4183118

Http://www.cnblogs.com/jhxk/articles/1633578.html

Many developers in the database design often do not have much to consider char, varchar type, some do not pay attention to, because the storage price has become more and more cheap, forget the beginning of some basic design theory and principles, this reminds me of the present young man, big hand a wave A renminbi from his hands slipped away, in fact, I think whether it is a man or a person, do the development or the details of the grasp directly decided a lot of things. Of course, some people do not understand their differences at all, and choose one. Here I would like to make a simple analysis of them, of course, if there is no place to ask for advice.

1.CHAR。 Char is convenient to store fixed-length data, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether your stored data reached 10 bytes, to take up 10 bytes of space, insufficient to automatically fill with spaces, sotrim () may be used multiple times while reading.

2.VARCHAR。 Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used.from the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

3.TEXT。 Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.

4.NCHAR, NVARCHAR, NTEXT。 These three kinds of names from the first three more than the previous "N". Itrepresents a character that is stored as a Unicode data type。 We know that characters, the English character only need a byte storage is enough, but the number of Chinese characters, need two bytes of storage, English and Chinese characters at the same time prone to confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, That is, the English character is also represented in two bytes. The length of the nchar and nvarchar is between 1 and 4000. Compared to char and varchar, nchar and nvarchar store up to 4,000 characters, whether in English or Chinese characters, while char and varchar can store up to 8,000 English and 4,000 Chinese characters. Can seeIt is convenient to use nchar, nvarchar data types without worrying about the characters entered, but there are some losses in the amount of English stored.

so generally, if it contains Chinese characters, use Nchar/nvarchar, if pure English and numbers, with Char/varchaR

I summarize their differences as follows:

Char,nchar fixed length, high speed, occupy space, need to handle
Varchar,nvarchar,text variable length, small space, slow speed, no need to handle
NCHAR, NVARCHAR, ntext processing Unicode codes

Second article:

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] 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

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.

October 16, 2014 11:34:19

The data type storage for SQL Server database is really not good enough to learn, I think I used to be the author of the present young people, read a little information, the author said the summary:

1. Correspondence between data types:

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

nvarchar (max)-----ntext;

varbinary (max)----image.

2. Now try to store data without the Text,ntext,image type.

3. In space, it is appropriate to use varchar (in fact, as far as possible, it is now the era of Big data); In terms of efficiency, the key is to find a trade-off point based on the actual situation.

4. Use nchar, nvarchar data type not to worry about the characters entered is English or Chinese characters, more convenient, but in the storage of English number of some losses. So generally, if it contains Chinese characters, use Nchar/nvarchar, if pure English and numbers, with Char/varchar

5.

Sites that support multiple languages should consider using Unicode nchar or nvarchar data types to minimize character conversion issues.

Use char If you want the data values in the column to be close to the same size.

If you want the data value in the column to be significantly different in size, use varchar.

Use nchar if you want all data items in the column to be close to the same size.

Use nvarchar if you want the size of the data items in the column to vary widely.

If SET ansi_padding is OFF when the CREATE table or ALTER table is executed, a char column that is defined as NULL is treated as varchar.

6.

1) If the amount of data is very large, and can be 100% to determine the length and save only ANSI characters, then Char
2) can determine the length is not necessarily ANSI characters or, then use nchar;
3) uncertain length, to query and want to use the index, use the nvarchar type bar, set them to 400;
4) Do not query the words have nothing to say, with nvarchar (4000)

7.

Char,nchar fixed length, high speed, occupy space, need to handle
Varchar,nvarchar,text variable length, small space, slow speed, no need to handle
NCHAR, NVARCHAR, ntext processing Unicode codes

Differences between char, varchar, text, and nchar, nvarchar, ntext in SQL Server (GO)