In the Latin1_General collation, ss is treated like ß (German sharp s) in comparisons:

'ss' = 'ß' --> true

Note that this is also true with LIKE:

'ss' LIKE 'ß' --> true

But if a '%' is part of the search pattern, things become complicated:

'ss' LIKE 'ß%' --> true
'ß' LIKE 'ss%' --> false (!!!)

Notably, the following are all true:

'ß' LIKE '%ss' --> true
'ß' LIKE '%ss%' --> true
'ß' LIKE 'ss%%' --> true
'ßa' LIKE 'ss%' --> true

Take a look at the tests with two ß:

'ßß' LIKE 'ssss%' --> false (guessed that)
'ßßa' LIKE 'ssss%' --> false (now that's a surprise)
'ßßaa' LIKE 'ssss%' --> true

Alright, there's a pattern in this behavior:

'ßßßaa' LIKE 'ssssss%' --> false
'ßßßaaa' LIKE 'ssssss%' --> true

My guess is that LIKE is optimized for the special case of patterns that end with '%' and contain no other wildcards. It seems that the comparison always fails if the searched text is shorter than the non-wildcard part of the pattern.

Bron:

http://www.sqlservercentral.com/Forums/Topic375531-338-1.aspx