Basic introduction and difference of varchar and nvarchar in SQL _mssql

VARCHAR (n)

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: coffee and I

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 can choose varchar, while the field value exists more double-byte (Chinese, Korean, etc.) characters with nvarchar

Above is a summary introduction, through the above introduction, you can know.

varchar (4) You can enter 4 letters, or you can enter two characters

nvarchar (4) can lose four characters, can also lose 4 letters, but up to four

The difference between char, varchar, nchar, nvarchar

For a string field in a program, SQL Server has char, varchar, nchar, nvarchar four types to correspond (temporarily regardless of text and ntext), open the database, the four types are often vague, here to do a comparison.

Fixed-length or variable-length

So-called fixed-length is constant, when the entered data length does not reach the specified length, it is automatically filled with an English space, so that the length reaches the corresponding length; the var prefix indicates that the actual storage space is longer, such as Varchar,nvarchar variable length character data will not be filled with spaces , the exception is that the text store is also variable length.

Unicode or non-Unicode

In a database, English characters require only one byte of storage, but Chinese characters and many other non-English characters require two bytes of storage. If the English and Chinese characters exist simultaneously, due to the number of space occupied, it is easy to create confusion, resulting in the reading of the string is garbled. The Unicode character set is created to resolve incompatible problems with the character set, all of which are represented in two bytes, that is, English characters are also represented in two bytes. The prefix n represents Unicode characters, such as Nchar,nvarchar, which use the Unicode character set.

Based on the above two points to see the field capacity

Char,varchar up to 8,000 English, 4,000 Chinese characters
Nchar,nvarchar can store 4,000 characters, whether English or Chinese

Use (personal preference)

If the amount of data is very large, and can be 100% to determine the length and save only ANSI characters, then Char
Can determine the length is not necessarily ANSI characters or, then use nchar;
For very large data, such as article content, use ntext

Other general nvarchar

Comparison of characteristics of char, varchar, nchar and nvarchar

CHAR

Char stores fixed-length data conveniently, and the index on the Char field is highly efficient, such as defining char (10), which takes up 10 bytes of space regardless of whether the data you store reaches 10 bytes.

VARCHAR

Store variable length data, but storage efficiency is not high char, if the possible value of a field is not fixed length, we only know that it can not exceed 10 characters, it is defined as VARCHAR (10) is the most cost-effective. 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.

TEXT

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

NCHAR, NVARCHAR, NTEXT

These three kinds of names look more "N" than the previous three. Compared with char and varchar, nchar and nvarchar store up to 4,000 characters, whether in English or 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 generally, if you have Chinese characters, use Nchar/nvarchar, if you have pure English and numbers, use Char/varchar.