SQL: Collation

To identify the collation of the database:

select DATABASEPROPERTYEX('SharedServices1_Search_DB', 'Collation')


SELECT NAME, COLLATION_NAME FROM sys.Databases
 ORDER BY DATABASE_ID ASC



To identify the SQL Server instance collation:

SELECT SERVERPROPERTY('collation') AS SQLServerCollation



How to Fix: Cannot resolve the collation conflict between blah blah blah in the equal to operation

SELECT ID FROM Table1 t1
INNER JOIN Table2 t2 on t1.id = t2.id
WHERE Table1.Col2 COLLATE DATABASE_DEFAULT = Table2.Col2 COLLATE DATABASE_DEFAULT


(Sometimes the collation conflict happens when the collation settings in your machine are different from production's, and you've created a copy of the production database in your local database server. Then you're doing a compare, or something else.)


SELECT * FROM temp1 t1
inner join temp2 t2 on t1.empid = t2.empid
WHERE t1.empname collate Latin1_General_CI_AS = t2.empname