SQL Server search for "ὡ" (U+1F61) matches all?

Question

One of our QA engineers stumbled on this one:

    SELECT DisplayName
    FROM Products
    WHERE (DisplayName LIKE N'%ὡ%')

This query matches 100% of the DisplayName values even though none of them contains "ὡ" (U+1F61). DisplayName is nvarchar(max). We'd like to prevent this from happening. Thoughts?

Answer 1

Yep, looks like Martin Smith was correct about a 100 collation group. The example below (tried on 2014) proves that:

declare @t table (
    ValueSQL nvarchar(20) collate SQL_Latin1_General_CP1_CI_AS,
    ValueWin nvarchar(20) collate Latin1_General_CI_AS,
    ValueWin100 nvarchar(20) collate Latin1_General_100_CI_AS
);

insert into @t
select 'Abc', 'Abc', 'Abc';

SELECT case when t.ValueSQL like N'ὡ%' then t.ValueSQL end as [MatchSQL],
    case when t.ValueWin like N'ὡ%' then t.ValueWin end as [MatchWin],
    case when t.ValueWin100 like N'ὡ%' then t.ValueWin100 end as [MatchWin100]
FROM @t t;