The difference between varchar and nvarchar

Source: Internet
Author: User

The Unicode character set is created to resolve incompatible problems with character sets, all of which are represented in two bytes, which is also represented in two bytes .

If still for this tangle, just look at the back of the commentary, make a decision.

In general, if you use the Chinese or other special characters, I will use the type of n beginning, otherwise the direct use of the Var start.

What is the difference between varchar and nvarchar in SQL Server.?

A variable length of n bytes and non-Unicode character data. n must be a numeric value between 1 and 8,000. The actual length of the byte that the storage size is the input data, not the N bytes.
nvarchar (n)
Variable-length Unicode character data containing n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice times the number of characters entered.
Two fields have field values: me and coffee.
The varchar field occupies 2x2+6=10 bytes of storage, while the nvarchar field occupies 8x2=16 bytes of storage space.
If the field value is only English selectable varchar, and the field value has more double-byte (Chinese, Korean, etc.) characters
, the nvarchar char varchar nvarchar distinguishes the online Help:



Instructions for use

Length Description

char (n)

Fixed length

Index efficiency high program inside use trim to remove extra whitespace

n must be a numeric value between 1 and 8,000, with a storage size of n bytes

varchar (n)

Become longer

Efficiency not char high flexibility

n must be a numeric value between 1 and 8,000. The actual length of the byte that stores the input data, not the N bytes

text (n)

Become longer

Non-Unicode data

No length specified

nchar (n)

Fixed length

Handles Unicode data types (all characters are represented by two bytes)

The value of n must be between 1 and 4,000. Twice times the storage size is n bytes

nvarchar (n)

Become longer

Handles Unicode data types (all characters are represented by two bytes)

The value of n must be between 1 and 4,000. The storage size of bytes is twice times the number of characters entered. The length of the data character entered can be zero

ntext (n)

Become longer

Handles Unicode data types (all characters are represented by two bytes)

No length specified

In general, if you have Chinese characters, use Nchar/nvarchar, if pure English and numerals, with Char/varchar
Here's a good summary.
Http:// Many developers do database design often do not have too much to consider char, varchar type, some do not pay attention to, Because storage prices are getting cheaper, forget the beginning of some of the basic design theory and principles, this reminds me of the young people now, big hand a wave of the renminbi from his hand slipped away, in fact, I think whether it is a person or do, do the development, the details of the grasp directly determine a lot of things. Of course, there are some people who do not understand their differences at all, and choose a random one. Here I would like to make a simple analysis of them, of course, if there is a wrong place I hope everyone will advise.

1, CHAR. Char stores fixed-length data easily, and the index on char fields is highly efficient, for example, the definition of char (10), no matter whether you are storing data to 10 bytes, to take up 10 bytes of space, not enough automatically filled with space, so in the reading time may have to use more than the trim ().

2, VARCHAR. Store variable length data, but storage efficiency is not high char. If the possible value of a field is an unfixed length, we only know that it cannot exceed 10 characters, and it is most cost-effective to define it as VARCHAR (10). The actual length of the varchar type is the actual length of its value +1. Why "+1"? This byte is used to save how much length is actually used. Consider from the space, use varchar suitable, consider from the efficiency, use char suitable, the key is according to the actual situation to find the tradeoff point.

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

4, NCHAR, NVARCHAR, NTEXT。 These three kinds of names look more "N" than the previous three. It represents a character that is stored in a Unicode data type. We know the characters, English characters only need one byte of storage is sufficient, but the number of Chinese characters, the need for two bytes of storage, English and Chinese characters at the same time can cause confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, The English character is also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with char and varchar, nchar and nvarchar store up to 4,000 characters, both English and Chinese, while char and varchar can store up to 8,000 English and 4,000 Chinese characters. You can see that the use of nchar, nvarchar data types do not have to worry about the input characters are English or Chinese characters, more convenient, but in the number of storage in English some loss. So in general, if you have Chinese characters, use Nchar/nvarchar, if you have pure English and numbers, use Char/varchar
their differences are summed up in:
Char,nchar fixed length, fast, occupy a large space, need to deal with
Varchar,nvarchar,text indefinite length, small space, slow speed, no need to deal with
NCHAR, NVARCHAR, ntext processing Unicode code to find a piece of information.



In SQL Server, a single byte is used to store data.


It is the use of Unicode to store data. 

Chinese characters stored in SQL Server are saved to two bytes (typically Unico encoded), and English characters are saved to the database if the field is of type varchar, you will only occupy One byte, and if the field is of type nvarchar, you will occupy two bytes
Under normal circumstances, we use varchar You can also store Chinese characters, but if you encounter an operating system that is an English-language operating system and when support is not comprehensive, text identifier in SQL Server storage varchar. There will be garbled (shown as??). And normally, the host will support the Chinese environment, so if you use varchar to store the data, which is not found in the development phase. In most cases, in the distribution department

Of course, use nvarchar storing English characters increases the storage space by one times. But when storage costs are already low, prioritizing compatibility can give you more benefits.
So that in the design time should try to use nvarchar to store the data. Use varchar to store only when you are sure that the field will not be saved in Chinese, there will be no problem.But. If the host is an English operating system and does not support the Chinese environment, then the problem comes out. All varchar fields will become garbled when they are stored in Chinese (shown as??). ). and generally you will not know this is because you use the wrong data type to store the resulting, you try to install Chinese fonts, try to set the operating system language environment ... None of this solves the problem, the only thing that solves the problem is the type character of the database field nvarchar(or nchar). Friends who are familiar with project management should know that it is a terrible thing to change the database at the time of the agency.

another great benefit of using nvarchar is that you don't have to consider the difference between the two characters when judging strings. 

Author: The Land of Fire
This article copyright to the author and blog Park, Welcome to reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to the original connection, otherwise retain the right to pursue legal responsibility.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.