Differences in SQL Server char, varchar, nchar, nvarchar

(1) Definition:

Char: fixed length, storing ANSI characters, insufficient complement English half-width space.

NCHAR: fixed length, storing Unicode characters, insufficient complement to English half-width space

VARCHAR: variable length, storing ANSI characters, automatically changing according to data length.

NVARCHAR: variable length, storing Unicode characters, automatically changing according to data length.

nvarchar (n): A variable-length Unicode character data that contains 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. The input data character length can be zero.

varchar[(n)]: variable-length, non-Unicode character data with a length of n bytes. n must be a numeric value between 1 and 8,000. Storage size is the actual length of bytes of input data, not n bytes. The input data character length can be zero.

varchar stores variable length data, but storage efficiency is not high, if the value of a field may be not fixed length, we only know it cannot exceed 10 characters, 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 byte is used to hold the length that is actually used.
From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

NOTE: ANSI mainly stores data in single byte, which is generally suitable for English. Our commonly used Chinese characters need to be stored in two bytes, so we will use Unicode data type, otherwise the data read out may be garbled.

(2) Difference:

① from the storage mode, nvarchar is stored by character, while varchar is stored in bytes;

② from storage, varchar saves space because the storage size is the actual length of bytes, while nvarchar is a double-byte storage;

③ in use, if the storage contents are English characters and no Chinese characters and other language symbols, we suggest using varchar , including the use of Chinese characters nvarchar, because nvarchar is the use of Unicode encoding, that is, the uniform character encoding standard, Will reduce the likelihood of garbled characters;

④ if the conversion between different languages may be involved, it is recommended to use nvarchar.

(2.1) char vs. varchar

CHAR (10)

varchar (10)

Write "abc"
Char loses hard disk 10 bytes = "abc" length + 7 null characters
varchar loses the hard drive 3 bytes = "abc" Length

var: variable length, which dynamically reallocate storage space based on the size of the actual stored data.

Without var: fixed length, storing 3 characters is also to occupy 10 bytes, will automatically fill 7 spaces.

Note: If the stored data exceeds 10 bytes, both fixed-length and variable-length errors will be made.

(2.2) Char and NCHAR

char () does not have a data type of N and stores two-byte characters, such as Chinese, and occupies 2 bytes; stores English, numbers, etc. each character occupies 1 bytes.

NCHAR () with n, whether stored in Chinese or English, numbers, etc., each character occupies two bytes.

Note: These data types without n are most often set to 8000, and these data types with n can be set to a maximum length of 4000 and the actual storage space is 8000 bytes.

CHAR (8000)

varchar (8000)

NCHAR (4000)

nvarchar (4000)

N prefix: N for conversion to Unicode format

INSERT into student (Name,birtiday) VALUES (N ' Kobe Bryant ', 1995-06-01)

(3) Advantages and disadvantages:

Nvarchar advantages: When judging the string can not consider the difference between the two characters in English, you can avoid the program garbled problem.

Disadvantage: Storing English characters will increase storage space by one more times. But given the low cost of storage, prioritizing compatibility gives you more benefits, without the efficiency of a varchar high.

(4) Why use nvarchar?

with n prefixes, n denotes Unicode characters, that is, all characters account for two bytes, Nchar,nvarchar

Character, the English characters only need one byte storage is sufficient, but the Chinese character is numerous, requires two bytes of storage, English and Chinese characters are prone to confusion, the Unicode character set is to solve the problem of incompatible character sets, all of its characters are represented by two bytes, That is, the English character is also represented in two bytes.

(5) A brief introduction to Var:

With the Var prefix, it means that the actual storage space is getting longer, Varchar,nvarchar

The so-called length is fixed, when the input length of the data does not reach the specified length will be automatically filled with English space after it, so that the length of the corresponding length, and the variable length character data will not be filled with spaces, the exception is that the text storage is also variable length.

(6) How do I use these types?

If you are sure of the length of the stored data and do not pack in Chinese, you can choose the char type.

If you are sure of the stored data length, but may include Chinese, you can choose the nchar type.

If you are unsure of the length of the stored data, store only English, the best use of the number varchar

If you are unsure of the length of the stored data, it is possible to have Chinese, you can choose the nvarchar type, and in SQL Server2005 is the more commonly used character data type.

(7) Supplement

TEXT

Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.
NCHAR, NVARCHAR, NTEXT
These three kinds of names from the first three more than the previous "N". Compared to char and varchar, nchar and nvarchar store up to 4,000 characters, whether in English or Chinese characters, while char and varchar can store up to 8,000 English and 4,000 Chinese characters.

It can be seen that the use of nchar, nvarchar data types without worrying about the input characters are English or Chinese characters, more convenient, but in the storage of English number of some losses.
So generally, if it contains Chinese characters, use Nchar/nvarchar, if pure English and numbers, with Char/varchar.

Differences in SQL Server char, varchar, nchar, nvarchar