sql - CHAR与VARCHAR以及加入时的后果

原文 标签 sql sql-server

CHAR vs. VARCHAR and the ramifications when joining

As this previous posting which discussed when would want to use CHAR over VARCHAR pointed out, one can gain performance advantages when the values stored are approximately the same length. Certainly I would selected CHAR as the datatype for storing a 2-character State code data.

I would just like to confirm as a sanity check that this means that when one performs a SQL that filters on this column that the filtered value must be padded with enough blanks to equal the defined length of the char field.

For example, assume a table "CODE_TABLE" where column 10_CHAR_CODE is defined as a CHAR(10):

SELECT * FROM CODE_TABLE WHERE 10_CHAR_CODE = 'ABCDE '

and that if 10_CHAR_CODE is a "logical" foreign key to another table but no specific relation integrity constraint exists, that the values stored in the 10_CHAR_CODE Lookup table should ideally be of the same datatype (Char(10) to avoid having to perform aan inefficient RTRIM function when joining.

(We have a baby size data warehouse and DBAs argue that Referential Integrity constraints are inefficient and so we end up having inconsistant datatype definitions of data across tables.)

Are my impressions accurate?

Answer

Trailing space is ignored in string comparisons in SQL Server. There is no need to RTRIM it yourself (which would make the condition unsargable)

翻译

正如this先前的文章所讨论的,该文章讨论了何时要使用CHAR而不是VARCHAR的情况,当存储的值大约相同的长度时,可以获得性能上的优势。当然,我会选择CHAR作为存储2个字符的状态码数据的数据类型。

我只想确认一下,这就是说,当执行在此列上进行过滤的SQL时,必须用足够的空格填充所过滤的值以等于char字段的定义长度。

例如,假设一个表“ CODE_TABLE”,其中第10_CHAR_CODE列定义为CHAR(10):

SELECT * FROM CODE_TABLE WHERE 10_CHAR_CODE ='ABCDE'

并且如果10_CHAR_CODE是另一个表的“逻辑”外键,但不存在特定的关系完整性约束,则理想情况下,存储在10_CHAR_CODE查找表中的值应为相同的数据类型(Char(10),以避免必须执行效率低下的操作)加入时使用RTRIM功能。

(我们有一个婴儿大小的数据仓库,DBA认为引用完整性约束的效率很低,因此最终导致表中数据的数据类型定义不一致。)

我的印象准确吗?
最佳答案
在SQL Server中的字符串比较中,尾随空格将被忽略。无需自己RTRIM进行操作(这将使条件无法确定)
相关推荐

sql - Oracle列值修改

sql - SQL选择性能最高1 VS选择1

sql - 按月,日最接近现在的SQL顺序

sql - CallableStatement + registerOutParameter +多行结果

mysql - 根据联接更新单个表中的行

sql - 使用单个SQL语句检索两个COUNT

java - 如何通过使用SQL WHERE子句搜索包含单引号的字符串

c# - C#数据库插入(ASP.NET)-ExecuteNonQuery:CommandText属性尚未初始化

sql - 动态地向表中插入多行

sql - SQL查询以选择父名称和子名称