若单讲效率,大数据量下测试表明无明显差别,效率高不高关键是看索引如何建很多人都说varchar比char节省存储空间,但我觉得不确切: 不知道SQL SERVER内部是如何存varchar数据的?我猜想是这样的:SQL SERVER存储记录时,每页最大的存储量为8060字节,例如记录有个varchar(100)数据,它应该仍是按最大值100来分配这个字段在每个页中的空间,所以要说varchar比char节省存储空间,也顶多只是节省每个记录所在的页的存储空间,对于数据库总的空间来说几乎没有影响假如不是如我上面所说,那么执行update t set col='abcdef' where col='',col字段增加了6字节,那么这6字节往那里存?岂不是要挪动其后面所有的记录或作指针连接了?
固定长度 (char) 或可变长度 (varchar) 字符数据类型。char[(n)]长度为 n 个字节的固定长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为 n 个字节。char 在 SQL-92 中的同义词为 character。varchar[(n)]长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度,而不是 n 个字节。所输入的数据字符长度可以为零。varchar 在 SQL-92 中的同义词为 char varying 或 character varying。注释
如果没有在数据定义或变量声明语句中指定 n,则默认长度为 1。如果没有使用 CAST 函数指定 n,则默认长度为 30。将为使用 char 或 varchar 的对象被指派数据库的默认排序规则,除非用 COLLATE 子句另外指派了特定的排序规则。该排序规则控制用于存储字符数据的代码页。支持多语言的站点应考虑使用 Unicode nchar 或 nvarchar 数据类型以尽量减少字符转换问题。如果使用 char 或 varchar: 如果希望列中的数据值大小接近一致,请使用 char。
如果希望列中的数据值大小显著不同,请使用 varchar。
如果执行 CREATE TABLE 或 ALTER TABLE 时 SET ANSI_PADDING 为 OFF,则一个定义为 NULL 的 char 列将被作为 varchar 处理。 当排序规则代码页使用双字节字符时,存储大小仍然为 n 个字节。根据字符串的不同,n 个字节的存储大小可能小于 n 个字符。
关键字 SQL Server 中易混淆的数据类型
出处
近来在做数据库设计,有时候真弄不清SQL2000里的数据类型,所以摘了这篇文章。(1)char、varchar、text和nchar、nvarchar、ntext
char和varchar的长度都在1到8000之间,它们的区别在于char是定长字符数据,而varchar是变长字符数据。所谓定长就是长度固定的,当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充,使长度达到相应的长度;而变长字符数据则不会以空格填充。text存储可变长度的非Unicode数据,最大长度为2^31-1(2,147,483,647)个字符。后面三种数据类型和前面的相比,从名称上看只是多了个字母"n",它表示存储的是Unicode数据类型的字符。写过程序的朋友对Unicode应该很了解。字符中,英文字符只需要一个字节存储就足够了,但汉字众多,需要两个字节存储,英文与汉字同时存在时容易造成混乱,Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所有的字符都用两个字节表示,即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较:nchar、nvarchar则最多存储4000个字符,不论是英文还是汉字;而char、varchar最多能存储8000个英文,4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字,较为方便,但在存储英文时数量上有些损失。(2)datetime和smalldatetime
datetime:从1753年1月1日到9999年12月31日的日期和时间数据,精确到百分之三秒。
smalldatetime:从1900年1月1日到2079年6月6日的日期和时间数据,精确到分钟。(3)bitint、int、smallint、tinyint和bit
bigint:从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据。
int:从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据。
smallint:从-2^15(-32,768)到2^15-1(32,767)的整数数据。
tinyint:从0到255的整数数据。
bit:1或0的整数数据。(4)decimal和numeric
这两种数据类型是等效的。都有两个参数:p(精度)和s(小数位数)。p指定小数点左边和右边可以存储的十进制数字的最大个数,p必须是从 1到38之间的值。s指定小数点右边可以存储的十进制数字的最大个数,s必须是从0到p之间的值,默认小数位数是0。(5)float和real
float:从-1.79^308到1.79^308之间的浮点数字数据。
real:从-3.40^38到3.40^38之间的浮点数字数据。在SQL Server中,real的同义词为float(24)。
而varchar是变长类型的,当数据长度没有达到声明的长度的话,那么就只占数据长度那么大的空间。其实怎么应用要根据你的目的,比如你如果空间足够,那么对于一些小字段采用定长,那么就会提高效率。但是遇到一些需要声明很长字段,那么你如果用定长会造成空间的很大浪费,所以采用变长就较为实际了!
比如账号,我如果单纯追求效率就一定要牺牲存储空间,用char,2者不能兼顾吗
--其实牺牲不了多少存储空间使用char数据类型
具体还是根据你存储的需要来定。
varchar:效率低,占用空间小
char类型适合用于长度较一致的字段
反之用varchar类型 所以
邹建在书中说,char比varchar效率高,但又说不定长字段用varchar,没有矛盾
在已知字节数的时候用char比用varchar效率高
在不知的情况下 用char设置小了有可能不够 设置大了有可能浪费
所以要用varchar比较合适 因为它是可变长型的
回楼上.
char比varchar效率高 这句话我们要肯定是对的.而
不定长字段用varchar,也是空间和效率上的比较。
想一想如果一个表格有几百W条记录时候。其中一个字段为字符型(100)而对应的每条记录的长度不同.小的2位,大的100位。想一想在这种情况你会选那一种是varchar,还是char
既然他说char比varchar效率高,那么不定长字段用char比用varchar效率高,他建议用varchar,那岂不是效率低了
没矛盾吗
http://chinadba.cn
骄子数据库服务网
最具实战经验的sql server优化,管理,设计,培训专家目前网站已经被google,baidu收录.
当长度变化不大,或定长时,用Char
如果长度未知,且变化很大,建设用varchar
如果你觉得效率重要,就用CHAR
如果觉得效率无所谓,关键是不要浪费空间,就用VARCHAR
不知道SQL SERVER内部是如何存varchar数据的?我猜想是这样的:SQL SERVER存储记录时,每页最大的存储量为8060字节,例如记录有个varchar(100)数据,它应该仍是按最大值100来分配这个字段在每个页中的空间,所以要说varchar比char节省存储空间,也顶多只是节省每个记录所在的页的存储空间,对于数据库总的空间来说几乎没有影响假如不是如我上面所说,那么执行update t set col='abcdef' where col='',col字段增加了6字节,那么这6字节往那里存?岂不是要挪动其后面所有的记录或作指针连接了?
假如不是如我上面所说,那么执行update t set col='abcdef' where col='',col字段增加了6字节,那么这6字节往那里存?岂不是要挪动其后面所有的记录或作指针连接了?确实是这样的,因为一行只能存储在同一页中,假设一页中存储了2行,假设占了 200 字节,如果你将这2条记录都UPDATE长度为 8000,此时一页肯定无法放下2条记录,必定有一条记录会要移到另外一页中,这是一种较差的情况,oracle中叫“行迁移”,我不知道sql server中应该叫什么。我做了一个实验create table csdn1
(
rid int ,
content varchar(8000)
)
goinsert into csdn1 ( rid , content ) values(1,'FirstTextFirstTextFirstTextFirstTextFirstTextFirstText__11')
insert into csdn1 ( rid , content ) values(2,'FirstTextFirstTextFirstTextFirstTextFirstTextFirstText__22FirstTextFirstTextFirstTextFirstTextFirstTextFirstText__22FirstTextFirstTextFirstTextFirstTextFirstTextFirstText__22')
--插入2条记录以后,通过sysindexes 查看,可以知道这个表数据页为1页
--当indid列值为0时,列 dpages 是已用数据页的计数
select * from sysindexes where id = object_id ( 'csdn1')
result: dpages = 1
--此时我把2条记录更新为长度为8000
update csdn1 set content = REPLICATE ( '1',8000) select * from sysindexes where id = object_id ( 'csdn1')
result: dpages =2
--这时候,这个表占了2页.--现在清空记录,把表的content字段更新为 长度4000,此时数据页占用1页。
truncate table csdn1
insert into csdn1 ( rid , content ) values(1,'FirstTextFirstTextFirstTextFirstTextFirstTextFirstText__11')
insert into csdn1 ( rid , content ) values(2,'FirstTextFirstTextFirstTextFirstTextFirstTextFirstText__22FirstTextFirstTextFirstTextFirstTextFirstTextFirstText__22FirstTextFirstTextFirstTextFirstTextFirstTextFirstText__22')
update csdn1 set content = REPLICATE ( '1',4000)
select * from sysindexes where id = object_id ( 'csdn1')
result: dpages = 1
结论:
1:varchar型并不会在insert时申请最大长度的空间
2:varchar型字段最好不要进行长度变动很大的更新操作,这时会导致数据页迁移,及索引迁移,使索引碎片更加严重。
------------------
http://chinadba.cn
深圳骄子数据库服务网
最具实战经验的数据库优化,管理,设计,培训网站
GOCREATE TABLE t1(a char(8000))
CREATE TABLE t2(a varchar(8000))
GOINSERT t1 SELECT TOP 100 '' FROM syscolumns
INSERT t2 SELECT TOP 100 '' FROM syscolumns
GOEXEC sp_spaceused 't1'
EXEC sp_spaceused 't2'
GODROP TABLE t1,t2
-- 结果, 同样的100条记录, t1表的data空间是800KB, 则t2是8KB
-- SQL Server是如何存储数据的, 一看就明白.而在检索上, 就算不考虑索引, char是定长的, 移动到下一条记录, 只需要做固定长度的指针偏移即可. varchar则必须根据当前记录的长度算出下一个数据指针的偏移.
沒有什麽是一成不變的,視條件靈活處理。ps: zjcxc被樓上的召喚出來了
1:varchar型并不会在insert时申请最大长度的空间,因此其存储空间比char小(char型字段即使在数据为null时也会分配固定空间)
2:varchar型字段最好不要进行长度变动很大的更新操作,这时可能会导致数据页迁移,及索引迁移,使索引碎片更加严重,因此从update这点上看varchar比char要差。
3:有人说char类型适合用于长度较一致的字段,但我认为这同样适合于varchar类型,因为长度固定,你更新字段时长度一般也是固定的,此时不会导致上面2所说的问题,因此用varchar应该不会有问题
4:char比varchar效率高,我认为这不确切。
若是说select,char比varchar效率肯定要低,因为一方面char因为存储空间大导致了更多的I/O消耗,另一方面因为SQL在作比较时是按每个字符去比较的,在较短的字符串里查找比较肯定比在较长的字符串里查找比较效率更高一些;
若是说update,char比varchar效率要高,这个也需要具体分析,要看具体的数据。假如更新的数据不会导致数据页迁移(更新后的长度几乎不变),那么还是varchar效率高些,道理与上面的分析一样;假如更新的数据会导致数据页迁移,这可能使得char的效率更高些。因此只有当更新的数据会导致数据页的迁移时,更新char型字段才可能比更新varchar字段的效率高些,其余情况varchar都不会处于劣势。不知各位是否认同?
而我今天与问了我认识的唯一一位微软的技术工程师,他的原话是:"udpate的结果不会超过8k. 如果这条数据放不下,就把这条放到新的页.如果新页有空间,就基本对半上页的数据。"
http://chinadba.cn
骄子数据库服务网
最具实战经验的sql server优化,管理,设计,培训专家