Difference between SQL char and varchar data types

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.