0

I read a lot about "choosing the right collation" and similar stuff, but I still haven't a solution for the following problem:

We got the following constellation: We switched the database for our application from Advantage Database to MsSQL Server 2014.

All databases have the same collation "Latin1_General_CI_AS" (except of ReportServer and ReportServerTempDB). We chose this collation, because "Latin1_General_CS_AS" would have a big impact on our existing querys because the tablenames in querys would change to case-sensitive.

To retain the data-order of querys we created our string-columns with "Latin1_General_CS_AS".

The problem we actually have is that querys with joins on temptables fail because of collation conflicts. It is clear to me that a TempDB-table has "Latin1_General_CI_AS" as collation and throw an error when it get joined with a string-column of a physical table.

We could solve our problems, if it would be possible to build case-insensitive querys regardless of the database-collation.

The other way around we could solve the problem by changing the collation of the databases to "Latin1_General_CS_AS" and correcting all our tablenames in querys to be the same as the original tablename.

If the first solution i think about is not possible, does someone have a good advice how to solve that collation problem?

The second solution is actually not practicable. We never cared about the capitalization and have simply to much database-interactions as it would worth it to correct it all.

Is there maybe a third or a fourth solution for our problem?

Thx 4 help in advance.

Christian Schröder
  • 51
  • 3
  • you can convert the collation when you join - table a join table b on col1 collate Latin1_General_CI_AS = col2 – BeanFrog Dec 02 '15 at 11:19
  • If you use Unicode (nvarchar, nchar) you don't need any "right" collation. If you don't you have a bug that should be fixed first. There's no collation that can handle even the EU's languages (eg. Latin, German and Cyrillic). – Panagiotis Kanavos Dec 02 '15 at 11:20
  • @BeanFrog this isn't a solution, just a quick-fix. In some cases, there will be no error but the optimizer will *not* be able to use the underlying indexes – Panagiotis Kanavos Dec 02 '15 at 11:21

1 Answers1

0

To force the collations to max you can use the COLLATE statement See: https://msdn.microsoft.com/en-us/library/ms184391.aspx

SELECT
a.col1,b.col2
FROM 
table a join table b 
ON  a.col1 collate databse_default = b.col2 collate databse_default
Tom Page
  • 1,211
  • 1
  • 7
  • 8
  • Thanks for your answer, but as I said i read a lot of stuff to collations and i am aware of this possibilty. But this isn't a possible solution. We would have to change nearly every join with a temptable. – Christian Schröder Dec 02 '15 at 11:42