Sql Server 2008 And Unicode Character Comparison

It seems that SQL Server 2008 removes some unicode characters when comparing two strings. Consider the following table: CREATE TABLE [dbo].[Test]( [text] [nvarchar](50) NOT NULL,

Solution 1:

Okay, so a bit more digging shows this is almost certainly due to newer character, since this also works with the sql server 2008 equivalents of latin collation, but not the older versions, i.e. works with Latin1_General_100_CI_AS, but not with Latin1_General_CI_AS. To get a full list of the collations that correctly compare these strings I used:

IF OBJECT_ID('Tempdb..#T') IS NOT NULL
    DROP TABLE #T;
IF OBJECT_ID('Tempdb..#V') IS NOT NULL
    DROP TABLE #V;

CREATE TABLE #V (A NVARCHAR(50), B NVARCHAR(50));
INSERT #V (A, B) VALUES (N'it᧠', N'it');

CREATE TABLE #T (Collation VARCHAR(500), Match BIT);

DECLARE @SQL NVARCHAR(MAX) = (SELECT N'INSERT #T (Collation, Match) 
                                        SELECT ''' + Name + ''', CASE WHEN A = B COLLATE ' + name + ' THEN 1 ELSE 0 END
                                        FROM    #V;'
                                FROM sys.fn_helpcollations()
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)');

EXECUTE sp_executesql @SQL;

SELECT  *
FROM    #T
WHERE   Match = 0;