Moving Oracle data (and index) to SQL-Server - capitalization in data

Question

I'm trying to move data from an Oracle (10g) database to SQL-Server (2008). I also want the indexes to be re-created on the SQL-Server side. However, in Oracle, there is a primary key defined on the first two fields, and it has data like this:

VALUE3   FOO4
VALUE4   FOO8
Value4   Foo8

When I get that data to SQL Server, it won't make that index, because of duplication of data. Oracle considers the case and thinks the 2nd and 3rd records are different.

Suggestions?

Answer 1

The issue is the collation on SQL Server.
The collation is case insensitive, so it sees "VALUE4" and "Value4" as the same thing. You need to change the collation to be case sensitive, in order to be able to apply the primary key constraint.

You can read more about it, and how to change the collation on SQL Server in this article.