I have a table with a unique key that includes an
NVARCHAR(50) column (correct or not, but is there). So, when trying to insert
C (doesn't matter the order of the insert) it breaks on the 2nd insert due to collation issues. Here is the error:
(1 row(s) affected) Msg 2601, Level 14, State 1, Line 16 Cannot insert duplicate key row in object 'dbo.testT' with unique index 'IX_TestT'. The duplicate key value is (C).
Database default collation is
Latin1_General_CI_AS. Spent some time looking on how to solve it, without changing too much the already existing structure, but can't find a way to getting working. Tried different collations and combinations, everything fails. Read (here and here) about character expansions and so on, still stuck. Here is a sample code that I'm using to replicate the issue, feel free to modify and recommend anything that could help solve this.
CREATE TABLE testT ( [Default_Collation] [NVARCHAR] (50) COLLATE DATABASE_DEFAULT, [Latin1_General_CI_AS] [NVARCHAR] (50) COLLATE Latin1_General_CI_AS, [Latin1_General_CI_AI] [NVARCHAR] (50) COLLATE Latin1_General_CI_AI, [SQL_Collation] [NVARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS); CREATE UNIQUE CLUSTERED INDEX [IX_TestT] ON [dbo].[testT] ([Default_Collation]) ON [PRIMARY] GO INSERT INTO testT SELECT N'Șc', --COLLATE Latin1_General_CI_AS N'Șc', --COLLATE Latin1_General_CI_AS N'Șc', --COLLATE Latin1_General_CI_AS N'Șc' --COLLATE Latin1_General_CI_AS INSERT INTO testT SELECT N'C' --COLLATE Latin1_General_CI_AS ,N'C' --COLLATE Latin1_General_CI_AS ,N'C' --COLLATE Latin1_General_CI_AS ,N'C' --COLLATE SQL_Latin1_General_CP1_CI_AS SELECT * FROM testT; DROP TABLE testT;