Dig into the differences between char varchar text and nchar nvarchar and ntext

The database design of many developers often don't have much consideration char, varchar types, have a plenty of didn't pay attention to, because the store is becoming more and more cheap price, forget the initial 1 some basic design theory and the principle, it made me think of today's young people, big hands and 1 yuan they slipped from his hand, actually I think whether it is to be all right, do the development, the details of the master directly decided to a lot of things. And then of course there's the one who doesn't know the difference at all, so they just pick one. Here I would like to make a simple analysis of them, of course, if there is a wrong place I hope you can advise.

1, CHAR. CHAR is very convenient for storing fixed-length data. The index on the CHAR field is very efficient. For example, if you define char(10), it will take up 10 bytes of space no matter whether you store the data up to 10 bytes or not.

2, VARCHAR. Stores variable length data, but not as efficiently as CHAR. If the possible value of a field is of variable length, we only know that it cannot exceed 10 characters, and it is most cost-effective to define it as VARCHAR(10). The actual length of type VARCHAR is the actual length of its value plus 1. Why plus 1? This 1 byte is used to store the actual length used. Considering the space, varchar is suitable. In terms of efficiency, char is suitable. The key is to find a balance 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 have more "N" in their names than the previous three. It means that it stores characters of the Unicode data type. We know that character, English character only need 1 byte storage is enough, but many Chinese characters, need two bytes storage, English and Chinese characters exist at the same time easy to cause confusion, Unicode character set is in order to solve the problem of character set that are not compatible, it all characters are expressed with two bytes, the English character is represented by two bytes. nchar and nvarchar are between 1 and 4000 in length. Compared with char and varchar, nchar and nvarchar store up to 4,000 characters, whether in English or Chinese. char and varchar can store up to 8,000 English and 4,000 Chinese characters. It can be seen that when using the data types nchar and nvarchar, you don't have to worry about whether the characters you input are in English or Chinese. It is more convenient, but there is some loss in the number of characters you store in English.

So 1 in general, nchar/nvarchar for Chinese characters, char/varchar for English and Numbers
I summarize their differences as follows:
CHAR, NCHAR fixed length, fast speed, large space, need to be processed
VARCHAR, NVARCHAR, TEXT are variable length, small space, slow speed, no need to deal with
NCHAR, NVARCHAR, NTEXT handle Unicode code