CHAR vs. VARCHAR and the ramifications when joining

Question

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?

from a couple of things that i've read you can actually get better performance with joining on integers than on varchar columns. :-D — DForck42, May 12 '11 at 14:30

score 7 · Accepted Answer · answered May 12 '11 at 13:10

7

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

answered May 12 '11 at 13:10

Martin Smith

438,706
87
741
845

score 1 · Answer 2 · answered May 12 '11 at 13:16

Martin appears to be correct. You might want to test on the particular collation you use in your database. Here is some test code:

DECLARE @ten_char_field char(10),
        @ten_char_varchar varchar(10)

SET @ten_char_field = 'ABCD'
SET @ten_char_varchar = 'ABCD'

SELECT @ten_char_field, CASE WHEN @ten_char_field = 'ABCD' THEN 1 ELSE 0 END,
        @ten_char_varchar, CASE WHEN @ten_char_varchar = 'ABCD' THEN 1 ELSE 0 END

CHAR vs. VARCHAR and the ramifications when joining

2 Answers2