Sort Issue - SQL Server 2005

Question

I would like to understand this error that I get on an instance of SQL Server 2005. The error below:

Cannot resolve sort conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AI" in equal operation.

Both databases included in a simple query have Latin1 General CI AI , but earlier SQL Latin1 General CP1 CI AI . I changed the sorting trying to match them, but still get the above error every time I try to join the temp table with a table from a user database. I have no problem creating temporary tables.

Answer 1

When you join, you can use COLLATE to change the sorting on the fly to make them the same:

select c.name, o.orderid from customer as c inner join order as o on c.custid = o.custid COLLATE SQL_Latin1_General_CP1_CI_AI 

Assuming cust is SQL_Latin1_General_CP1_CI_AI and order is some other sort, the above query will fix the problem.

Answer 2

Look at the table design that both fields you use have the same sorting settings.

 ALTER TABLE <table> ALTER COLUMN <column> VARCHAR(200) COLLATE Latin1_General_CI_AI GO 

Answer 3

If you create a temporary table and your database has a different sort except TempDB (which has the same mapping as the installation-based system), then comparison / search / join is probably a problem. A simple trick to get around this is using database_default:

 CREATE TABLE #test2 ( col1 varchar(12) COLLATE database_default ) go 

Re. Changing Database Mapping and Working with TempDB Objects

Did you restore all indexes after changing the sort order of the database?

Answer 4

I ran into very similar problems when moving a database between servers with different sortings. The database encounters the original sort, but since the new server sort (and therefore the tempdb database sort) is different, it causes problems when creating the temp table and tries to join it.

If you say that the database sort is the same as the server sort, then I think you should have several columns that use a different sort. (You can change the sorting column by column).

In any case, I always deal with these problems simply by changing the sorting of the entire database to match the target server after it is restored. To make PITA manually, but fortunately there is a tool .

Answer 5

From what I remember, if you use the tempDb built-in memory, it is recreated as needed. Thus, if your Model has a different sort, the newly created db (e.g. tempDb) will inherit it from the model.