Database collation different from tempdb collation

Question

I just received the source code and database of an application from my client (it was developed by another company, from a different country) and the application is throwing some exceptions related to the objects collation:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

I saw that in my case this is caused when a Stored Procedure creates a #temp table and this #temp table is used in a comparison with tables from the application (the application database uses SQL_Latin1_General_CP1_CI_AS and tempdb uses Latin1_General_CI_AS).

I'm adding COLLATE SQL_Latin1_General_CP1_CI_AS to the CREATE TABLE #TEMPTABLE statement, however this database has many Stored Procedures that may use #temp tables.

How can I fix this faster without breaking the other databases/applications?

Answer 1

Does this database need to be on this Instance? If not, then as @Max Vernon suggested in a comment on the question, you could install this on an Instance that is created with a default Collation of SQL_Latin1_General_CP1_CI_AS. Perhaps you could even get away with using SQL Server Express Edition, if the database is < 10 GB and doesn't require any Enterprise Edition features.

But if that is not an option, then what you are already doing,

adding COLLATE SQL_Latin1_General_CP1_CI_AS to the CREATE TABLE #TEMPTABLE statement

is pretty much the only reliable thing you can do.

Since you do not want to adversely affect the operation of the app that was delivered by your client, you cannot change the Collation of the new database. And in fact, changing the DB's default Collation wouldn't fix your current problem as it wouldn't update the Collation of the column in the tables in that DB. It would only affect the Collation of string literals and variables, and you would only notice a difference when those two things are the only things being compared (since the Collation of a column would override the implied Collation of string literals and variables).

Since you don't want to adversely affect your existing DBs on your SQL Server, it wouldn't make sense going through the trouble of changing the current Instance's Collation to SQL_Latin1_General_CP1_CI_AS.

Considering that the new DB and related code were developed (and hopefully tested!) on an Instance that had SQL_Latin1_General_CP1_CI_AS as the default Collation, you really need to make it so that the queries in the new DB behave as they did in the environment in which they were created. You can only do this by forcing the Collation of string columns in temporary tables by adding the COLLATE clause (as you doing). Well, technically speaking you can change the temporary tables into table variables since their default Collation will be taken from the current database, but that is a more intrusive change as the Query Optimizer doesn't handle table variables in the same way as temporary tables (and there are other notable differences).

In the COLLATE clause, you could use either DATABASE_DEFAULT or continue using SQL_Latin1_General_CP1_CI_AS. Both are equivalent here since it is not likely that the Collation of the new DB will ever change (and if it does, that means you will have a new DB from the client that does not include your changes anyway ;-).

---

FYI: There are not many differences between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. They both use the same Locale -- 1033 -- and same Code Page -- 1252. The main differences are:

• The SQL_ Collations have been deprecated for a long time and should be avoided if possible
• They treat apostrophes and dashes within words differently
• The non-SQL_ Collations allow for some expansions / normalization offered by Unicode that will equate more characters than the SQL_ Collations.

Answer 2

It depends, for example connecting to a database or databases sourced in another country where their variant collation is the standard location in their region.

If so, then there is a reason that you need to have two (or more) collations such as joining with a database (or table) that has a variant collations.

Probably the best choice would be to include the COLLATE DATABASE_DEFAULT with you queries. This will handle many (but not all) collation issues.

A very simple example below:

SELECT [ITEM_KEY],[PERSON_NAME]
FROM   [TempDB].[dbo].[ITEM] AS SC
  JOIN [NonStandardCollation].[dbo].[REMOTEITEM] AS NSC
WHERE  SC.ITEM_KEY COLLATE DATABASE_DEFAULT =  NSC.ITEM_KEY

In this case, especially if dealing with several other collations, it will simplify the code on your server.