SQL Server auto-trim of varchar value in equal comparison but not like comparison
My initial answer suggested that the ANSI_PADDING flag set to OFF may be to blame for the difference in behavior. However, this is incorrect; this flag only has an effect on storage, but not equality comparison.

The difference stems from Microsoft's implementation of the SQL standard. The standard states that when checking for equality, both strings left and right of the equality operator have to be padded to have the same length. This explains the following results:

insert into test_padding (varchar_clmn, nvarchar_clmn) values ('space ', 'nspace ')
go
-- equality for varchar column
select count(*) from test_padding where varchar_clmn = 'space' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space    ' --returns 1
-- equality for nvarchar column
select count(*) from test_padding where nvarchar_clmn = 'nspace' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace    ' --returns 1


The LIKE operator does not pad its operands. It also behaves differently for VARCHAR and NVARCHAR column types:

-- likeness for varchar column
select count(*) from test_padding where varchar_clmn like 'space' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space    ' -- returns 0
-- likeness for nvarchar column
select count(*) from test_padding where nvarchar_clmn like 'nspace' -- returns 0
select count(*) from test_padding where nvarchar_clmn like 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn like 'nspace    ' -- returns 0


The behavior of the LIKE operator for the ASCII type is SQL Server-specific; for the Unicode type it is ANSI-compliant.