We were using SQL_Latin1_General_CP1_CI_AS collation till couple of months back and recently moved to Latin1_General_100_CI_AS_KS_WS collation. We created new database in Latin1_General_100_CI_AS_KS_WS and it worked in all the development machines without any issue .So we changed the testing servers to Latin1_General_100_CI_AS_KS_WS collation and it performed well. After some load testing we had to modify one SP which introduced temp tables. As it is related to load testing we first applied in the test server and it rocked. But when we take the same sp to development machines the a problem raised because we didn’t change the sql server instances of our development machines to Latin1_General_100_CI_AS_KS_WS which is a good time consuming process. That means the temp database is in different collation compared to our project database.

For example consider a simple scenario.We created a Address database in the Latin1_General_100_CI_AS_KS_WS collation where our sql server instance is in SQL_Latin1_General_CP1_CI_AS .We have a Person table (Id,Name) and Address Table (Id,PersonId,Address) which has person id as foreign key and we need to select details of some persons based on a particular person name list.Earlier we had a ‘in’ keyword based implementation and that we changed to temp table based implementation where the temp table is a table which has one column and it will be joined with the Address table to get the details. Ok its time to see some sql.

The error msg was “Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.”. This means the temp DB is still in SQL_Latin1_General_CP1_CI_AS collation and our DB is in Latin1_General_100_CI_AS_KS_WS collation which doesn’t allow us to do a comparison on strings.So as a hack or quick work around in development environment, we modified the query as follows which specifies the collation on the comparison.