欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

sql char和varchar数据类型两者区别对比

程序员文章站 2022-05-26 19:38:09
...

本文章介绍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 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 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

8907

89093

2000000

char

9188

96530


两表存储空间比较

代码如下 复制代码

Testchar:73.94M
Testvarchar:7.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.