SQL Server reference: Selection of char and varchar

Char (or nchar) and varchar (or nvarchar) are the most common data types used to store character data in databases. Because they have the same majority of features, when selecting which type of character data to use,

There may be some questions. However, through some comparisons and tests, you can easily select them based on actual needs.

1. Comparison of char and varchar

The following are some results of using char and varcahr.

(1) Data Storage overhead.

A. Each varchar column requires two additional bytes to reflect the length of the stored data.

B. For each char column that can be null, some bytes (vacancy chart) are required to reflect the NULL data.

C. No matter how long the actual data is, char allocates storage space according to the defined length.

D. varchar allocates data storage space based on the actual data length.

(2) null value.

The Null Value of the. Char column occupies storage space.

B. The Null Value of the varcahr column does not occupy storage space.

C. Insert the same number of null values. The insertion efficiency of the varchar column is significantly higher than that of the char column.

(3) Insert data. The efficiency of char is significantly lower than that of varchar, regardless of whether the columns involved in data insertion are indexed.

(4) update data

If no index is created for the updated column, the efficiency of char is lower than that of varchar, which is slightly different. If an index is created, the efficiency varies greatly.

(5) modify the structure

A. no matter whether the type of the added or deleted column is Char or varchar, the operation can be completed quickly and there is no difference in efficiency.

B. for increasing the column width, char and varchar have obvious efficiency differences. Modifying the varcahr column does not take much time, but it takes a long time to modify the char column.

(6) data retrieval. Regardless of whether an index is used, varchar data retrieval is slightly better than char scanning.

2. Select Char or varchar.

(1) suitable for char:

A. The length of each row in the column is basically the same, and the length cannot exceed 50 bytes;

B. Frequent data changes and less data retrieval needs.

C. The length of a column does not change, so it is costly to modify the width of a char column.

D. The column does not contain a large number of null values.

E. You do not need to create too many indexes on the columns. Too many indexes have a great impact on data changes in the char column.

(2) suitable for archar;

A. The length of each row in the column varies greatly.

B. There are very few data updates in the column, but the query is very frequent.

C. Columns often have no data, which is a null value or a null value.