SQL Server char(1) and char(2) column

Question

I need a table for bookkeeping all types in the data model. I don't have that many types but a fair deal of 'em. Ideally short descripive names would work well.

So I did this:

CREATE TABLE EntityType
(
    EntityTypeID char(2) PRIMARY KEY,
    EntityTypeName varchar(128) NOT NULL
)

And put some data into the table:

INSERT INTO EntityType VALUES ('A', 'Type1')
INSERT INTO EntityType VALUES ('B', 'Type2')

But that I can query like this baffles me:

DECLARE @pEntityType char(1)
SET @pEntityType = 'A'
SELECT ''''+EntityTypeID+'''', EntityTypeName 
FROM EntityType 
WHERE EntityTypeID = @pEntityType

The result yields 'A ', and there's whitespace in that literal.

My understanding is that there's an implicit conversion that's converting char(1) -> char(2)

I'm not complaining, but what's the rationale behind this?

Answer 1

char() is something which allocates the characters depending on the number specified. It would not adjust back to the length of the data you pass.

Hence if you specify 2 in char(2), then your field will always have 2 characters.

Answer 2

In sql server a column of type Char(x) will always be x chars long. That means that if you insert an amount of chars that is shorter than x, the value will be padded with whitespaces. To avoid this, you can use Varchar(x) instead.

Answer 3

The rationale behind this is that char(2) is a fixed-length data type. It means that it contains two characters and cannot contain less or more than that. By default, implicit conversion fills unspecified characters with spaces. To make this data type a variables length where value could be 1 or 2 characters long, make it a varchar(2).

Answer 4

As I see it, the rationale is that if you said that you wanted a fixed-length column of two-characters, then the data is only correct if it is two-characters long, and the conversion is just another one of the ways in which the database makes sure it has correct data.

That is of course also based on the fact that there is a mechanism for variable-length columns, as people have said.

Answer 5

The reason for the behaviour is that trailing spaces are ignored in string comparisons in SQL Server. This happens irrespective of whether fixed or variable length data types are being used as can be seen from the below.

DECLARE @EntityType TABLE
(
    EntityTypeID varchar(2) PRIMARY KEY,
    EntityTypeName varchar(128) NOT NULL
)

INSERT INTO @EntityType VALUES ('A ', 'Type1')
INSERT INTO @EntityType VALUES ('B', 'Type2')

DECLARE @pEntityType varchar(1)
SET @pEntityType = 'A'
SELECT ''''+EntityTypeID+'''', EntityTypeName 
FROM @EntityType 
WHERE EntityTypeID = @pEntityType