Shulou(Shulou.com)06/01 Report--

This article is about the differences between char, varchar, nchar, nvarchar and text. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The difference between char, varchar, nchar, nvarchar and text

1. If there is a var prefix, it means that the actual storage space is longer, varchar,nvarchar.

The so-called fixed length is the fixed length, when the length of the input data does not reach the specified length, it will automatically fill in English spaces after it, so that the length will reach the corresponding length, when you enter the character greater than the specified number, it will intercept the excess part; while variable length character data will not be filled with spaces, with an exception, text stores variable length.

2. N represents Unicode characters, that is, all characters occupy two bytes, nchar,nvarchar

Among the characters, only one byte is enough to store English characters, but there are many Chinese characters that need two bytes of storage. It is easy to cause confusion when English and Chinese characters exist at the same time. The Unicode character set is created to solve the incompatible problem of the character set. All its characters are represented by two bytes, that is, English characters are also represented by two bytes.

3. Let's look at the field capacity based on the above two points.

Char (n), varchar (n) up to 8000 English, 4000 Chinese characters n represents the actual length of bytes of input data nchar (n), nvarchar (n) can store 4000 characters, whether in English or Chinese characters n represents the number of characters

4. Use (personal preference)

a. If the amount of data is very large, the length can be determined 100%, and only ansi characters are saved, then char

b. To determine the length is not necessarily an ansi character or, then use nchar

c. For very large data, such as article content, use nText

d. Other generic nvarchar

Characteristic comparison

1 、 CHAR . CHAR is very convenient to store fixed-length data, and indexing on CHAR fields is highly efficient, such as defining char (10), so it takes up 10 bytes of space regardless of whether the data you store reaches 10 bytes or not.

2 、 VARCHAR . Store variable length data, but the storage efficiency is not as high as CHAR. If the possible value of a field is not 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 the VARCHAR type is the actual length of its value + 1. Why "+ 1"? This byte is used to keep the actual length used.

From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, it is appropriate to use char. The key is to find the tradeoff point according to the actual situation.

3 、 TEXT . Text stores variable length non-Unicode data with a maximum length of 2 ^ 31-1 (2147483647) characters.

4 、 NCHAR 、 NVARCHAR 、 NTEXT . These three are more "N" than the first three in terms of name. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese characters, while char and varchar can store up to 8000 English characters and 4000 Chinese characters. You can see that when using nchar and nvarchar data types, you don't have to worry about whether the input characters are English or Chinese characters, which is more convenient, but there is some loss in the quantity when storing English.

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

Thank you for reading! About char, varchar, nchar, nvarchar, text what is the difference between sharing here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.