Collation Got Me
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.