本文章介绍mssql server中char和varchar数据性能对比分析，详细的利用实例来说明两者的关系，有需要了解的朋友可以参考一下。
1. char
固定长度，最长n个字符。
2. varchar
最大长度为n的可变字符串。
（n为某一整数，不同，最大长度n不同）
如果我们将长度设为10：char(10), varchar(10), 然后我们将值设为： ‘apple’。
Char(10) 存储的值会是：’apple ’.
Varchar(10) 存储的值会是：’apple’.
Char 和 Varchar 不能存储 Unicode 字符。
| 数据类型 | 可以存储 unicode | 固定长度 |
|---|---|---|
| char | 不 | 是 |
| nchar | 是 | 是 |
| varchar | 不 | 不 |
| nvarchar | 是 | 不 |
char和varchar区别：
varchar必char节省空间，但在效率上比char稍微差一些。
说varchar比char节省空间，是因为varchar是可变字符串，比如：用varchar(5)存储字符串“abc”，只占用3个字节的存储空间，而用char(5)存储，则占用5个字节（“abc ”）。
说varchar比char效率稍差，是因为，当修改varchar数据时，可能因为数据长度不同，导致数据迁移（即：多余I/O）。其中，oracle对此多余I/O描述的表达是：“行迁移”（Row Migration）。
看个实例
本次测试的SQL SERVER 2000 的char 与 varchar。
测试分三组,每次增加插入的行数,脚本如下:
| 代码如下 | |
| 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 |
|
实验结果:
|
插入行数 |
数据类型 |
Cpu |
duration |
|
50000 |
varchar |
2359 |
21203 |
|
50000 |
char |
2344 |
22250 |
|
100000 |
varchar |
4156 |
44500 |
|
100000 |
char |
4172 |
44186 |
|
2000000 |
varchar |
07 |
093 |
|
2000000 |
char |
9188 |
96530 |
两表存储空间比较
| 代码如下 | |
|
Testchar:73.94M |
|
从国站找到一则实例
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 languages should 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.
下载本文