ChadD ChadD - 6 months ago 15
SQL Question

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)