Analysis on the differences of SQL server data types char, nchar, varchar and nvarchar

Time:2021-11-21

In SQL server, when we set character fields, there are often many data types for us to choose, such as char nchar varchar nvarchar, etc. which one should we choose? The following describes the difference.

char:Fixed length, non Unicode character data, with a length of N bytes. The value range of n is 1 to 8000, 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 8000. 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 length of the input data can be 0 characters. Varchar in sql-2003 is charactervarying or charactervarying.

nchar:Fixed length Unicode character data of n characters. The value of N must be between 1 and 4000 inclusive. The storage size is twice n bytes. Nchar’s sql-2003 synonyms are nationalchar and nationalcharacter.

nvarchar:Variable length Unicode character data. The value of n is between 1 and 4000 inclusive. Max indicates that the maximum storage size is 2 ^ 31-1 bytes. The storage size is twice the number of characters entered + 2 bytes. The length of the input data can be 0 characters. Nvarchar’s sql-2003 synonyms are national character varying and national character varying.

char,varchar:Up to 8000 English and 4000 Chinese characters

nchar,nvarchar:It can store 4000 characters, whether in English or Chinese

char,nchar:Fixed length, fast speed, large space, need to be handled

varchar,nvarchar:Long, slow, small space, no processing

Fixed length is a fixed length. When the input data length does not reach the specified length, it will be automatically filled with English spaces to make the length reach the corresponding length.

Variable length character data will not be filled with spaces. The exception is that text stores variable length.

1、char。Char is very convenient to store fixed length data. The index efficiency level on the char field is high. For example, if you define char (10), whether the data you store reaches 10 bytes or not, it will occupy 10 bytes of space. If it is insufficient, it will be automatically filled with spaces. Therefore, trim () may be used many times when reading.

2、varchar。Variable length data is stored, but the storage efficiency is not as high as char. If the possible value of a field is not of fixed length, we only know that it cannot exceed 10 characters. It is most cost-effective to define it as varchar (10). The actual length of a varchar type is the actual length of its value + 1. Why “+ 1”? This byte is used to save the actual length used. In terms of space, varchar is suitable; In terms of efficiency, char is appropriate. The key is to find the trade-off point according to the actual situation.

3、nchar、nvarchar、ntext。These three are more “n” than the first three in terms of names. It indicates that characters of Unicode data type are stored. We know that in characters, English characters only need one byte to store, but there are many Chinese characters, which need two bytes to store. When English and Chinese characters exist at the same time, it is easy to cause confusion. Unicode character set is produced to solve the incompatibility of character set. All its characters are represented by two bytes, that is, English characters are also represented by two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese; Char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you don’t have to worry about whether the input characters are English or Chinese. It is more convenient, but there is some loss in the quantity of storing English.

So generally speaking, if it contains Chinese characters, use nchar / nvarchar. If it is pure English and numbers, use char / varchar.

Recommended Today

Single case mode can also play flowers

1、 Singleton mode 1. What is singleton mode (1) Singleton mode [singleton pattern:] definition: Ensure a class has only one instance, and provide a global point of access to it. Ensure that a class has only one instance and provide a global access point to it (only instance objects are allowed to be obtained through […]