SQL Server data type analysis of the difference between char, nchar, varchar and nvarchar _mssql

In SQL Server, when we set character fields, there are often a number of data types for us to choose from, such as: char nchar varchar nvarchar, then we should choose which one? Here's a Yi Yilai description of the difference.

char: fixed length, non-Unicode character data, n bytes in length. The value range of n is 1 to 8,000, and the storage size is n bytes. The SQL2003 synonym for Char is character.

varchar: variable length, non-Unicode character data. The value range of n is 1 to 8,000. Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the input data plus 2 bytes. The data entered can be 0 characters in length. The varchar in SQL-2003 is charvarying or charactervarying.

nchar:N-Character fixed-length Unicode character data. The n value must be between 1 and 4,000 (inclusive). Storage size is twice times n bytes. The SQL-2003 synonyms for nchar are Nationalchar and nationalcharacter.

nvarchar: variable-length Unicode character data. n values are between 1 and 4,000 (inclusive). Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is twice times + 2 bytes of the number of characters entered. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are nationalcharvarying and nationalcharactervarying.

Char,varchar: up to 8,000 English, 4,000 Chinese characters

Nchar,nvarchar: can store 4,000 characters, whether English or Chinese characters

Char,nchar: fixed length, fast, occupy a large space, need to deal with

Varchar,nvarchar: long, slow, take up small space, no need to deal with

Fixed-length is a constant, when the input data length does not reach the specified length will be automatically filled with English spaces behind it, so that the length of the corresponding length.

Variable-length character data will not be filled with spaces, and, more exceptionally, the text store is also variable length.

1, Char. char Storage fixed-length data is very convenient, char field index efficiency level, such as the definition of char (10), then whether you store data to reach 10 bytes, will take up 10 bytes of space, insufficient automatically fill with space, So the trim () may be used more than once in the reading.

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, 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 generally, if you have Chinese characters, use Nchar/nvarchar, if you are in plain English and numbers, use Char/varchar.