The difference between char nchar varchar nvarchar text ntext in SQL

Type	Length	Instructions for use	Length description
CHAR (n)	Fixed length	High index efficiency, using trim inside the program to remove extraneous blanks	n must be a numeric value between 1 and 8000, with a storage size of n bytes
VARCHAR (n)	Variable length	Efficiency no char high flexibility	n must be a numeric value between 1 and 8000. Storage size is the actual length of bytes of input data, not n bytes
Text (n)	Variable length	Non-Unicode data	No length specified
NCHAR (n)	Fixed length	Working with Unicode data types (all characters are represented by two bytes)	The value of n must be between 1 and 4000, and the storage size is twice times of n bytes
nvarchar (n)	Variable length	Working with Unicode data types (all characters are represented by two bytes)	The value of n must be between 1 and 4000. 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	Working with Unicode data types (all characters are represented by two bytes)	Do not refer to length

in general, if the Chinese characters, use Nchar/nvarchar, if it is pure English and numbers, with Char/varchar

Char, it is convenient to store a fixed length of data, the index on the Char field is high, such as the definition of char (10), whether your storage is the data reached 10 bytes, to take up 10 bytes of space, insufficient to automatically fill with spaces, so the time to read may be used more than trim () .

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 Varchart type is +1 of the 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.

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 look more "N" than the previous three, which represents characters stored in Unicode data types. We know that characters, English character only a byte storage is enough, but a large 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. nchar, the length of the nvarchar is between 1 and 4000. Compared to char, varchar, nchar, nvarchar stores up to 4,000 characters, whether in English or Chinese characters, while char, varchar can store up to 8,000 English and 4,000 Chinese characters. You can see the use of nchar, nvarchar data type do not worry 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

Their differences are summarized as follows:

Char, nchar fixed length, high speed, occupy space, need to handle

varchar, nvarchar, text variable length, small space, slow speed, no processing

nchar, nvarchar, ntext processing Unicode code

varchar uses a single byte in SQL to store data, nvarchar uses Unicode to store data, Chinese characters stored in SQL are saved as two bytes (typically encoded in Unico), English characters are saved to the database, if the field type is varchar , only one byte is consumed, and if the field is of type nvarchar, it takes two bytes.

Under normal circumstances, we can also use varchar to store Chinese characters, but if the operating system is an English-language operating system and the English font support is not comprehensive, in the SQL store text character is varchar will appear garbled. And under normal circumstances, the main will support the Chinese environment, so if using varchar to store data, in the development phase is not found, in most cases, in the deployment.

Of course, the use of nvarchar storage English characters to increase the storage space, but at the cost of storage is already very low, the first consideration of compatibility will bring you more benefits

So when you try to use nvarchar to store your data in design, you only use varchar to store it when you make sure that the field doesn't save Chinese.

The difference between char nchar varchar nvarchar text ntext in SQL