Conversion between char, varchar, text, nchar, nvarchar, and ntext

From http://www.cnblogs.com/ebaidu/

"

Many developers often do not consider the char and varchar types when designing databases. Some developers do not pay attention to them because the storage price is getting cheaper and cheaper, I forgot some of the basic design theories and principles at the beginning. This reminds me of the young people who have moved away from their hands with a wave of RMB. In fact, I want to be a human or a human, development is good, and the grasp of details directly determines a lot of things. Of course, there are still some people who just don't figure out their differences, so they just choose one. Here I want to make a simple analysis on them. Of course, if there is something wrong with them, I hope you can give me some advice.

1. Char. it is convenient for Char to store fixed-length data, the index on the char field is highly efficient. For example, if you define char (10), whether or not the data you store has reached 10 bytes, it takes up 10 bytes of space, insufficient values are automatically filled with spaces, so trim () may be used multiple times during reading ().

2. varchar. Variable-length data is stored, but the storage efficiency is not as high as char. If a field may have an unfixed length value, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as varchar (10. The actual length of the varchar type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length. in terms of space, it is appropriate to use varchar. in terms of efficiency, char is suitable. The key is to find a trade-off based on the actual situation.

3. Text. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.

4. nchar, nvarchar, and ntext. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with Char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese. Char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.

generally, if the string contains Chinese characters, use nchar/nvarchar, for pure English text and numbers, use Char/varchar

to summarize their differences:
char, nchar fixed length, fast speed, large space, to be processed
varchar, nvarchar, and text cannot be long, the space is small, and the speed is slow, no need to process Unicode codes
nchar, nvarchar, and ntext

"