1. char
Fixed length, n characters at most.
2. varchar
A variable string with a maximum length of n.
(N is an integer. The maximum length of n varies with different databases)
If we set the length to 10: char (10) and varchar (10), then we set the value to 'apple '.
Char (10) stores the value 'Apple '.
The value stored in Varchar (10) is 'Apple '.
Char and Varchar cannot store Unicode characters.
Data type |
Unicode storage |
Fixed length |
Char |
No |
Yes |
Nchar |
Yes |
Yes |
Varchar |
No |
No |
Nvarchar |
Yes |
No |
Differences between char and varchar:
Varchar requires char to save space, but it is slightly less efficient than char.
Varchar saves space than char because varchar is a variable string. For example, if varchar (5) is used to store the string "abc", it only occupies three bytes of storage space, char (5) occupies 5 bytes ("abc ").
Varchar is slightly less efficient than char because, when modifying varchar data, data migration (that is, redundant I/O) may be caused by different data lengths ). Oracle expresses this redundant I/O description as "Row Migration ).
View instances
Char and varchar of SQL SERVER 2000 in this Test.
The test is divided into three groups. The number of inserted rows is added each time. The script is as follows:
The code is as follows: |
Copy code |
Create table [dbo]. [testchar] ( [A] [int] IDENTITY (1, 1) not null, [B] [char] (200) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO Create table [dbo]. [testvarchar] ( [A] [int] IDENTITY (1, 1) not null, [B] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO -- Truncate table testvarchar -- Truncate table testchar Declare @ I as int Set @ I = 0 While @ I <50000 Begin Insert into testvarchar values (cast (@ I as varchar (200 ))) Set @ I = @ I + 1 If @ I = 50000 Break End Declare @ I as int Set @ I = 0 While @ I <50000 Begin Insert into testchar values (cast (@ I as char (200 ))) Set @ I = @ I + 1 If @ I = 50000 Break End |
Experiment results:
Number of inserted rows |
Data type |
Cpu |
Duration |
50000 |
Varchar |
2359 |
21203 |
50000 |
Char |
2344 |
22250 |
100000 |
Varchar |
4156 |
44500 |
100000 |
Char |
4172 |
44186 |
2000000 |
Varchar |
8907 |
89093 |
2000000 |
Char |
9188 |
96530 |
Comparison of two table buckets
The code is as follows: |
Copy code |
Test char: 73.94 M Testvarchar: 7.94 M |
Find an instance from a foreign website
Fixed-length (char) or variable-length (varchar) character data types.
Char [(n)]
Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
Varchar [(n)]
Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. storage size is the actual length in bytes of the data entered, not n bytes. the data entered can be 0 characters in length. the SQL-92 synonyms for varchar are char varying or character varying.
Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
Objects using char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page used to store the character data.
Sites supporting multiple versions ages shoshould consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar:
Use char when the data values in a column are expected to be consistently close to the same size.
Use varchar when the data values in a column are expected to vary considerably in size.
If SET ANSI_PADDING is OFF when create table or alter table is executed, a char column defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the character string, the storage size of n bytes may be less than n characters.