What field types do we need?

When the database defines fields of the char type, I wonder if you will hesitate to select which of the following char, nchar, varchar, nvarchar, text, and ntext fields? There are two possible results. One is the choice of frugal people: it is best to use a fixed length to save some space for the change, and the process will be faster, if you cannot set the length, you have to set the length as small as possible. The other is that it doesn't matter. Try to use a variable type and enlarge the length as much as possible.

In view of the current favorable situation where hardware is as cheap as radish, it makes little sense to be entangled in such a small problem. However, if you do not find out about it, you will always feel sorry for the overworked CPU and hard disk.

(The following description is only valid for SqlServer ):

1. Use the following queries with caution when using non-unicode:

Select f from t where f = n'xx'

Cause: the index cannot be used, because the database will convert f to unicode first and then compare it with N 'xx '.

2. The processing speed of char and varchar with the same length is almost the same (as described later)

3. The length of varchar does not affect the processing speed !!! (See the following explanation)

4. The total length of a column in an index can be up to 900 bytes. Therefore, nchar cannot create an index for varchar, char, and nvarchar with a length greater than 900.

5. indexes cannot be created on text or ntext.

6. In O/R Mapping, the attribute types of the corresponding entities are generally strings, and char [] is rarely used. Therefore, if mapping is reasonable, the types of variable length are more consistent.

7. Generally, the name in the basic data table basically uses the like '% xx %' method in the actual query, but this method cannot use the index. Therefore, for this field, the index is also created in white.

8. Other fields like remark do not need to be queried at all, so no index is required.

9. The storage of varchar is the same as that of string, that is, length {block}. Therefore, the length of varchar is irrelevant to the occupied space.

10. For a fixed-length field, extra space is required to store the NULL identifier. Therefore, if a char field contains many NULL values, unfortunately, your occupied space is larger than that without NULL (but this is not too large, because the NULL mark is stored in bits, but if you have only one NULL in a row, you need to identify it, in this case, you will waste 1 byte of space and sin !), At this time, you can use special identifiers for storage, such as 'nv'

11. Same as above. Therefore, indexes cannot take effect for such NULL queries. If you use a NULL marker instead, congratulations, you can use the index.

12. The comparison costs of char and varchar are the same. Now the key is to look at the cost of their index search. Because the search policies are the same, we should compare who occupies less space. If the number of characters is small, the char length is smaller than that of varchar. However, if the number is small, varchar may be less than char, it also depends on the enrichment of the actual filling value, such as varchar (3) and char (3). In theory, it should be char faster, but if it is char (10) and varchar (10). If the enrichment is only 30%, it should be varchar in theory. Because varchar requires extra space to store the block length, as long as the length (1-fillfactor) is greater than the storage space (as if it is 2 bytes), it will be faster than char with the same length.

13. nvarchar is slower than varchar, and it will take up double space for non-unicode characters. Why is this type introduced? Yes, it is for internationalization. For unicode data, sorting rules do not work for them, rather than when unicode characters process data in different languages, the sorting rule must be specified to work normally, so n type is of this benefit.

Summary:

1. If the data size is very large, and the length can be determined by 100% and the storage is only ansi characters, then char

2. Determine whether the length is not necessarily an ansi character or nchar;

3. You are not sure about the length. If you want to query and use indexes, set them to 400 in nvarchar format;

4. There is nothing to say if you do not query it. Use nvarchar (4000)

5. If you have a strong character, you can only use 3 and 4, and occasionally use 1. After all, this is an additional explanation. It is equivalent to telling others that I must use X-bit data.

In this way, is life much better? If you still don't understand, save some money to buy radishes.