Search code examples
stringt-sqlstring-concatenation

Strange behavior: T-sql char/string concatenation char

I have a strange behavior in t-sql. Giving the following test 

   DECLARE @a char(22) = 'John went to buy a car', @b char(15)= 'Tom went to pub';  
   SELECT IIF(1=1 , @a, @b )+'. He met a friend';

Output ok: John went to buy a car. He met a friend.

   SELECT IIF(1=2 , @a, @b )+'. He met a friend';

Output wrong:

    Tom went to pub       . He met a friend.

The concatenation of variable char allocates 22 chars if test is true, and 22 chars otherwise! Thus in char concatenation through IIF (but the same happens with CASE) the output is allocated for the first expression (@b variable is considered as 22 chars!) Any ideas?

Solution

  • An IIF is an expression and therefore needs a data type. This data type can't depend on what happens at execution time - it needs to be known at query compilation time.

    Therefore, given that the two inputs are a char(22) and a char(15), the data type picked by T-SQL for the expression as a whole is the 'wider' of these two, the more inclusive - namely, char(22).

    So whatever the execution-time result, the type of the result will be char(22). Which, as you have observed, means that sometimes the result is space-padded compared to the chosen input.

    Interestingly, I can't actually find this behaviour explicitly called out anywhere in the documentation for IIF. It says there

    Returns the data type with the highest precedence from the types in true_value and false_value. For more information, see Data Type Precedence (Transact-SQL)

    but the linked page on Precedence doesn't actually call out what happens for fixed-length character types such as char and varchar, unless I've missed it.