Collation Got Me

21. December 2011 Howto 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:

https://gist.github.com/1507324

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


2 thoughts on “Collation Got Me”

  • 1
    eliperelman on December 21, 2011 Reply

    Back end fail.

  • 2
    Nate Taylor on December 21, 2011 Reply

    More like Back end doing-exactly-what-it-should-have success.

Leave a Reply

Your email address will not be published. Required fields are marked *