The difference between SQL Server char nchar varchar nvarchar

Description and comparison of database storage types (nchar, char, nvarchar, varchar) for database SQL Server

1.char Type:

The English (ASCII) characters occupy 1 bytes, a Chinese character occupies 2 bytes, char is convenient to store the fixed length data, the index on the Char field is very efficient, such as the definition of char (10), then regardless of whether the data you stored is 10 bytes, it takes up 10 bytes of space. Because it is fixed length, the speed is high efficiency.

2.varchar Type:

Storing variable-length data, if a field's possible value is not fixed, we only know that it cannot exceed 10 characters, and it is 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 one byte is used to save the actual length of the used.

3.Nchar type and nvarchar type:

In order to convert with many other characters, such as Chinese, phonetic transcription, and so on, so these two types of database storage, whether English or Chinese characters, occupies two characters.

4. Note:

The type of 4.1. varchar is not filled with spaces, and char fills up until full;

For example: varchar (10), but its value is only "Qian", so its value in the database is "Qian", and char is different, such as char (10), its value is "Qian", and in fact it is "Qian" in the Database ( A total of 96 spaces after the Qian is filled with 100 bytes.  

Since char is fixed-length, it will be much faster than varchar! But the program to deal with a little trouble, to use trim and other functions to remove the space on both sides!

4.2. VarChar generally applies to English and numerals, nvarchar is suitable for Chinese and other characters, where n table Unicode constants, can solve the problem of conversion between multi-language character sets.

The above explanation:the varchar type of data in the database is for English is stored in one byte, and the Chinese characters are stored in two bytes, so that varchar can save up to 8,000 English, 4,000 characters, And this nvarchar means that as long as the type of a field is declared as nvarchar, the database is stored according to the number of bytes of the Chinese character regardless of the actual data (both English and Chinese). Therefore, you can store up to 4,000 lengths of values for the nvarchar type, either Chinese or English. Thus, it can be seen that the varchar is applicable to the storage of English, nvarchar the existence of Chinese characters is not the actual value of what is stored, but in the database in what way they store

5. Summary of differences:


5.1. Fixed length or variable length
The so-called length is fixed, when the length of the data to be saved will be automatically filled with English space after it, so that the length of the corresponding length, with the Var prefix, that is, the actual storage space is dynamic change, such as Varchar,nvarchar variable length character data will not be filled with spaces.

5.2.Unicode or non-Unicode
In a database, English characters require only one byte to store, but Chinese characters and many other non-English characters require two bytes of storage. If the English and Chinese characters exist simultaneously, because of the different occupied space, it is easy to cause confusion, resulting in the reading of the string is garbled. The Unicode character set is created to address the incompatibility of the character set, and all of its characters are represented in two bytes, meaning that the English character is also represented in two bytes. The prefix n represents Unicode characters, such as Nchar,nvarchar, which use the Unicode character set.

5.3. Maximum capacity for storage of several data types
Char,varchar up to 8,000 English, 4,000 kanji

Nchar,nvarchar can store up to 4,000 characters, whether in English or kanji

Reprinted from http://blog.csdn.net/blockhouse_fly/article/details/13768557