Collation Got Me

21. December 2011 Uncategorized 2

I was doing some SQL work today and was trying to insert a few thousand records into a user defined table type that would then be passed to a stored procedure to be operated on.

The data was really simple:

Id, ModuleId, Code.  The primary key was a composite key across all 3 fields.  To make sure I wasn’t putting duplicate data in, I ran my select statement with the distinct keyword

However, as I ran the snippet and inserted the data into my user-defined table type, I kept getting a PK violation.

At first, I thought it was a simple varchar vs nvarchar issue.  But changing the data type didn’t seem to help.  Finally I ran a query that showed 2 records it considered to be the same.  The ‘Code’ was:

  • N[MMn
  • N[mmN

Now the system this is coming from, those are both unique identifiers.  But because of how I defined my user type, they were treated as the same.  The change was to the definition of the user defined type to be:

Now that I chose the correct collation, it treated the 2 codes as different.  No more primary key violations.