sql - Why does the content of one scenario in a case decide whether the other's content should be right trimmed? -

my co-workers , have encountered strange behaviour in sql-server.

consider following code:

select =      case when 1=1         'a '     else         'b '     end + 'c' 

one should reasonable assume above code produces string 'a c'. , does.

however, if extend else's constant ('b ') additional space, strange happens:

select =      case when 1=1         'a '     else         'b  '     end + 'c' 

now result 'ac', space 'a ' has disappeared. reverse true:

select =      case when 1=0         'a  '     else         'b '     end + 'c' 

this yield 'bc'. whenever constant not being returned longer constant being returned, constant being returned right trimmed.

and it's right trim, because following code yield ' ac':

select =      case when 1=1         ' '     else         'b   '     end + 'c' 

is there reason behaviour? assumed might have cleaning strings trailing whitespace, why happen when constant not being returned longer?

the above behaviour have been observed on sql server 2014 express edition.

update:

as mentioned in comments, behaviour not appear reproducible on standard installations of sql server 2014.

it looks session has set ansi_padding off, trim trailing spaces varchar. try running set ansi_padding on (recommended setting).

set ansi_padding off;  --trims trailing spaces select =      case when 1=1         'a '     else         'b  '     end + 'c';  --does not trim trailing spaces set ansi_padding on;  select =      case when 1=1         'a '     else         'b  '     end + 'c'; 

edit:

per sql server ansi_padding documentation, setting should apply column storage, not expression results. asked behavior , sql server mvp erland sommarskog did regression tests using ancient sql server versions , identified behavior bug introduced in sql 7. corrected in sql 2000, ansi_padding on; ansi_padding off continues misbehave day.

i suggest change code not rely on undocumented , buggy behavior. bug may fixed in future sql server version or patch , break code. explicitly use rtrim function if application needs trailing spaces removed.