最近项目的调整主要涉及数据库字段长度的更改。在DB2中如果要修改一个char类型的字段的长度唯一的办法似乎就是删除表重建。所以,在varchar和char到底使用哪个的问题上,一直让人破费脑筋。从网上读到的一些资料,给这个复杂的问题一个答复:
SQL Server Performance Tuning for SQL Server Developers
[http://www.databasejournal.com/features/mssql/article.php/1466951]
Choose the Appropriate Data Types
While you might think that this topic should be under database design, I have decided to discuss it here because Transact-SQL is used to create the physical tables that were designed during the earlier database design stage.
Choosing the appropriate data types can affect how quickly SQL Server can SELECT, INSERT, UPDATE, and DELETE data, and choosing the most optimum data type is not always obvious. Here are some suggestions you should implement when creating physical SQL Server tables to help ensure optimum performance.
-
Always choose the smallest data type you need to hold the data you need to store in a column. For example, if all you are going to be storing in a column are the numbers 1 through 10, then the TINYINT data type is more appropriate that the INT data type. The same goes for CHAR and VARCHAR data types. Don't specify more characters for character columns that you need. This allows SQL Server to store more rows in its data and index pages, reducing the amount of I/O needed to read them. Also, it reduces the amount of data moved from the server to the client, reducing network traffic and latency.
-
If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. Although the VARCHAR data type has slightly more overhead than the CHAR data type, the amount of space saved by using VARCHAR over CHAR on variable length columns can reduce I/O, improving overall SQL Server performance.
-
Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O overhead.
-
If you need to store large strings of data, and they are less than 8,000 characters, use a VARCHAR data type instead of a TEXT data type. TEXT data types have extra overhead that drag down performance.
-
If you have a column that is designed to hold only numbers, use a numeric data type, such as INTEGER, instead of a VARCHAR or CHAR data type. Numeric data types generally require less space to hold the same numeric value as does a character data type. This helps to reduce the size of the columns, and can boost performance when the columns is searched (WHERE clause) or joined to another column.
Forum: Char OR VARCHAR?
[http://archives.postgresql.org/pgsql-sql/2001-03/msg00520.php]
There is *no* performance advantage of CHAR(n) over VARCHAR(n).
If anything, there is a performance lossage due to extra disk I/O
(because all those padding blanks take space, and time to read).
My advice is to use CHAR(n) when that semantically describes your data (ie, truly fixed-width data, like US postal codes), or VARCHAR(n) when that semantically describes your data (ie, variable-width with a hard upper bound), or TEXT when that semantically describes your data (ie, variable width with no specific upper bound). Worrying about performance differences is a waste of time, because there aren't any.
If the text data in a column varies greatly in length, use a VARCHAR
data type instead of a CHAR data type. Although the VARCHAR data type has slightly more overhead than the CHAR data type, the amount of space saved by using VARCHAR over CHAR on variable length columns can reduce I/O, improving overall SQL Server performance.
Several other people at the session who were familiar with the
performance effects of using char vs. varchar confirmed my advice. One person said his team was charged with deploying an application that used SQL Server. After deploying the application, the team found that it performed terribly. Upon inspecting the database, team members discovered that all the fields were varchar. They changed the fields to char, and the application now performs fine.
Here's the advice from IBM in from the DB2 Admin guide SC96-9003) Copyright IBM Corp. 1982, 1999 " Choosing CHAR or VARCHAR: VARCHAR
> saves DASD space, but costs a 2-byte overhead for each value and the additional processing required forvarying-length records. Thus, CHAR is preferable to VARCHAR, unless the space saved by the use of VARCHAR is significant. The savings are not significant if the maximum length is small or the lengths of the values do not have a significant variation. In general, do not define a column as VARCHAR(n) unless n is at least 18. (Consider, also, using data compression if your main concern is DASD savings. See "Compressing data in a table space or partition" in topic 2.6.2 for more information.)
If you use VARCHAR, do not specify a maximum length that is greater
than necessary. Although VARCHAR saves space in a table space, it
does not save space in an index, because index records are padded with blanks to the maximum length. Note particularly the restrictions on columns of strings longer than 255 bytes; for example, they cannot be indexed. These restrictions are listed in Chapter 3 of DB2 SQL Reference.
相关推荐
在本篇文章中我们给大家分享了关于MySQL中int、char以及varchar的性能对比的相关内容,有兴趣的朋友们学习下。
在MySQL数据库中,用的最多的字符型数据类型就是Varchar和Char.。这两种数据类型虽然都是用来存放字符型数据,但是无论从结构还是 从数据的保存方式来看,两者相差很大。而且其具体的实现方式,还依赖与存储引擎。我...
char和varchar在mysql中的效率怎样.docx
CHAR和VARCHAR相同的是:CHAR和VARCHAR都指定了字符长度,注意是字符长度。例如char(30) 和 varchar(30)表示都可以存30个字符。有一点要注意的是在utf8mb4编码中,每个字符占4个节点。在utf8中,每个字符占3个字节。...
oracle char,varchar,varchar2的区别和使用方法 非本人总结,但是说的挺好的! 欢迎下载
浅析Oracle中char和varchar2的区别 电脑资料.docx
在MySQL中,char、varchar和text类型的字段都可以用来存储字符类型的数据,char、varchar都可以指定最大的字符长度,但text不可以。 它们的存储方式和数据的检索方式也都不一样。 数据的检索效率是:char >...varchar:
char、varchar、nchar、nvarchar四种类型的区别char、varchar、nchar、nvarchar四种类型的区别
SQLServer中char、varchar、nchar、nvarchar的区别 SQLServer中char、varchar、nchar、nvarchar的区别.txt
你真的知道CHAR和VARCHAR类型在存储和读取时的区别吗? 还是先抛几条结论吧: 1、存储的时候,CHAR总是会补足空格后再存储,不管用户插入数据时尾部有没有包含空格。 2、存储的时候,VARCHAR不会先补足空格后再存储...
也就是说可以存放65532个字节的数据,起始位和结束位占去了3个字节,也就是说,在5.0.3以下版本中需要使用固定的TEXT或BLOB格式存放的数据可以在高版本中使用可变长的varchar来存放,这样就能有效的减少数据库文件的...
char是固定长度的,而varchar会根据具体的长度来使用存储空间,另外varchar需要用额外的1-2个字节存储字符串长度。 1). 当字符串长度小于255时,用额外的1个字节来记录长度 2). 当字符串长度大于255时,用额外的2个...
nchar_char_varchar与nvarchar有何区别?
可以将创建表的语句直接拷贝值sqldbx(连接db2数据库的工具)中修改字段的类型,如varchar2转化为varchar,number转化为integer。还有primary key、unique的变化。 2、表数据迁移 在Plsql中选择表——>右键——>qurey...
oracle中varchar2(byte)和varchar2(char) 细节决定成败,大家在设计数据库建表的时候一定要注意。
NULL 博文链接:https://wy649898543.iteye.com/blog/1446712
char与varchar的区别 char (13)长度固定, 如’www.jb51.net’ 存储需要空间 12个字符 varchar(13) 可变长 如’www.jb51.net’ 需要存储空间 13字符, 从上面可以看得出来char 长度是固定的,不管你存储的数据是多少...