The difference between data type char/varchar/nvarchar/nchar/text/ntext in SQL

, CHAR. Char is convenient to store the fixed-length data, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether you store data reached 10 bytes, to take up 10 bytes of space, insufficient to automatically fill with spaces, so you may want to read the time to use trim ().

2, VARCHAR. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the 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.

3, TEXT. Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.

4, NCHAR, NVARCHAR, NTEXT. These three kinds of names from the first three more than the previous "N". It represents a character stored in a Unicode data type. We know that characters, the English character only need a byte storage is enough, but the number of Chinese characters, need two bytes of storage, English and Chinese characters at the same time prone to confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, That is, the English character is also represented in two bytes. The length of the nchar and nvarchar is between 1 and 4000. 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 speaking, if it contains Chinese characters, use Nchar/nvarchar, if pure English and digital, with Char/varchar char,nchar fixed length, fast, occupy space, need to deal with
Varchar,nvarchar,text variable length, small space, slow speed, no need to handle
NCHAR, NVARCHAR, ntext processing Unicode codes

char (n)	fixed length	index efficiency elevation Use trim to remove extra whitespace	n   must be a number between 1 and 8,000, the storage size is   n   bytes
varchar (n)	variable length	efficiency without char high flexibility	N   must be a numeric value between 1 and 8,000. Store the actual length of bytes for the input data, instead of n   bytes
text (n)	length	non-Unicode data
nchar (n)	fixed length	handles Unicode data types (all characters are represented by two bytes)	n   values must be between 1 and 4,000 Between. The storage size is   n   bytes twice times
nvarchar (n)	variable length	processing Unicode data types (all characters use two 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
ntext (n)	variable length	handles Unicode data types (all characters are represented by two bytes)

Differences in data type Char/varchar/nvarchar/nchar/text/ntext in SQL