Previously I have written two different ways to find database collation SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS. One of blog reader jwwishart has posted another method for doing the same.
SELECT collation_name FROM sys.databases WHERE name = 'AdventureWorks'
Why You Need to Find Database Collation Before It Bites
Collation decides how SQL Server compares and sorts text. It controls whether ‘abc’ equals ‘ABC’, whether accents matter, and the order in which names sort. The name tells you most of it: CI means case insensitive, CS means case sensitive, and AI and AS do the same for accents.
Collation exists at three levels, and it is good to know how to check each one:
- Server:
SELECT SERVERPROPERTY('Collation'). This is also the collation of the system databases, including tempdb. - Database:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')is one more way, next to the methods above. - Column:
SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('Person.Address'). A column can have its own collation, different from the database.
The most common problem I see is the error “Cannot resolve the collation conflict”. It often happens when a user database has a different collation from the server. Temp tables are created in tempdb, so their text columns take the server collation, and a join to a user table then fails. The quick fix is to add COLLATE DATABASE_DEFAULT to the text columns when you create the temp table. You can also add a COLLATE clause to the comparison in the query itself.
When you compare two collations, look at the whole name, not only the CI or CS part. Two collations can both be case insensitive and still be different collations, for example a SQL collation and a Windows collation, and the conflict error can still appear between them. SELECT name, description FROM sys.fn_helpcollations() lists every collation with a short description, which helps when a name is hard to read.
Changing the collation of a database later is possible, but it does not change the existing columns, so it rarely solves the problem on its own. That is why I check the collation before I restore a database on a new server or create a new one. A minute spent checking saves hours of fixing.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
