SQL server data types: char and nchar, varchar and nvarchar, text and ntext?

Differences between varchar and nvarchar:

Varchar (n)
Variable-length and non-Unicode character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes (such as varchar (6). When the field is qqq, the actual space occupied by the database is 3 bytes, instead of 6 bytes, it can only store 6 bytes. Generally, Chinese occupies two bytes ).

Nvarchar (n)
Unicode data with a variable length of n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice the number of input characters.

The two fields have Field Values: Me and coffee.
The varchar field occupies 2 × 2 + 6 = 10 bytes of storage space, while the nvarchar field occupies 8 × 2 = 16 bytes of storage space.

If the field value is only in English, you can select varchar. If the field value contains many double-byte (Chinese, Korean, etc.) characters, use nvarchar.
In SQL Server, how do I select varchar and nvarchar?

Varchar uses a single byte to store data in SQL Server. nvarchar uses Unico to store data. when a Chinese character is stored in SQL Server, it is saved as two bytes (generally Unico encoding), and an English character is saved to the database. If the field type is varchar, only one byte is occupied, if the field type is nvarchar, it occupies two bytes.

Under normal circumstances, we can use varchar to store Chinese characters. However, if the operating system is an English operating system and the Chinese font is not fully supported, if the Chinese character is varchar in SQL Server, garbled characters (displayed as?) are displayed ??). In addition, the host normally supports Chinese environments. Therefore, if you use varchar to store data, you cannot find it in the development stage. in most cases, there will be no problems during deployment.

But! If the deployed host is an English operating system and the Chinese environment is not supported, the question is displayed. all varchar fields are garbled when they are stored in Chinese ??). In general, you don't know that this is because you use the wrong data type to store the data. You will try to install Chinese fonts, try to set the language environment of the operating system... these cannot solve the problem. The only solution is to set the database field type to nvarchar (or nchar ). anyone familiar with project management should know that it is terrible to modify the database at the deployment stage.

Another advantage of using nvarchar is that you do not need to consider the differences between Chinese and English characters when judging strings.

Of course, using nvarchar to store English characters will increase by a factor of storage space. However, given the low storage cost, compatibility will bring you more benefits.

　　Therefore, you should try to use nvarchar to store data during Design. varchar is used only when you ensure that this field does not save Chinese characters.

 

1. CHAR. CHARStorage of fixed-length dataThe indexing efficiency of the CHAR field is very high. For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space. 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 fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as VARCHAR (10. The actual length of the VARCHAR type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length. From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, char is suitable, and the key is to find a trade-off point based on the actual situation. 3. TEXT. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters. 4. NCHAR, NVARCHAR, and NTEXT. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with char and varchar,Nchar and nvarchar can store a maximum of 4000 characters, both in English and 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 do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours. Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains pure English characters and numbers, use char/varchar.

Differences between varchar and nvarchar:

Varchar (n)
Variable-length and non-Unicode character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes (such as varchar (6). When the field is qqq, the actual space occupied by the database is 3 bytes, instead of 6 bytes, it can only store 6 bytes. Generally, Chinese occupies two bytes ).

Nvarchar (n)
Unicode data with a variable length of n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice the number of input characters.

The two fields have Field Values: Me and coffee.
The varchar field occupies 2 × 2 + 6 = 10 bytes of storage space, while the nvarchar field occupies 8 × 2 = 16 bytes of storage space.

If the field value is only in English, you can select varchar. If the field value contains many double-byte (Chinese, Korean, etc.) characters, use nvarchar.
In SQL Server, how do I select varchar and nvarchar?

Varchar uses a single byte to store data in SQL Server. nvarchar uses Unico to store data. when a Chinese character is stored in SQL Server, it is saved as two bytes (generally Unico encoding), and an English character is saved to the database. If the field type is varchar, only one byte is occupied, if the field type is nvarchar, it occupies two bytes.

Under normal circumstances, we can use varchar to store Chinese characters. However, if the operating system is an English operating system and the Chinese font is not fully supported, if the Chinese character is varchar in SQL Server, garbled characters (displayed as?) are displayed ??). In addition, the host normally supports Chinese environments. Therefore, if you use varchar to store data, you cannot find it in the development stage. in most cases, there will be no problems during deployment.

But! If the deployed host is an English operating system and the Chinese environment is not supported, the question is displayed. all varchar fields are garbled when they are stored in Chinese ??). In general, you don't know that this is because you use the wrong data type to store the data. You will try to install Chinese fonts, try to set the language environment of the operating system... these cannot solve the problem. The only solution is to set the database field type to nvarchar (or nchar ). anyone familiar with project management should know that it is terrible to modify the database at the deployment stage.

Another advantage of using nvarchar is that you do not need to consider the differences between Chinese and English characters when judging strings.

Of course, using nvarchar to store English characters will increase by a factor of storage space. However, given the low storage cost, compatibility will bring you more benefits.

　　Therefore, you should try to use nvarchar to store data during Design. varchar is used only when you ensure that this field does not save Chinese characters.

 

1. CHAR. CHARStorage of fixed-length dataThe indexing efficiency of the CHAR field is very high. For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space. 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 fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as VARCHAR (10. The actual length of the VARCHAR type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length. From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, char is suitable, and the key is to find a trade-off point based on the actual situation. 3. TEXT. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters. 4. NCHAR, NVARCHAR, and NTEXT. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with char and varchar,Nchar and nvarchar can store a maximum of 4000 characters, both in English and 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 do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours. Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains pure English characters and numbers, use char/varchar.