Using collate: When to use and when not to use

Question

In one of my applications we had tsql like shown below

SELECT DISTINCT [Dblist].[DbName] AS [DbName]
FROM [Common].dbo.[Dblist]
WHERE dbname not in (
 SELECT [name]
 FROM master.dbo.sysdatabases )

For one of my customers it gave an error related to collation as the database Common (part of my application) has default collation sql_latin1_general_cp1_ci_as whereas master db has collation latin1_general_ci_as. I found a solution using collate and it worked. I want the solution to work on all collations.

SELECT DISTINCT [Dblist].[DbName] AS [DbName]
FROM [Common].dbo.[Dblist]
WHERE dbname not in (
 SELECT [name] COLLATE DATABASE_DEFAULT 
 FROM master.dbo.sysdatabases )

Now I am confused. Should I use COLLATE in all queries which involve system databases? When to use collate and when not to use?

Also, is the above way of using collate proper (like COLLATE DATABASE_DEFAULT)? Here DATABASE_DEFAULT is latin1_general_ci_as itself as it is the collation of master database. Then how did the solution work as they again don't match with Common database? I want solution which will work on all collations.

• Aaron Bertrand: Why are you using sysdatabases instead of sys.databases?
• IT researcher: @AaronBertrand Should i use sys.databases over sysdatabases? What is the diff?
• Aaron Bertrand: Well, sys.databases are the modern way to look at database metadata. sysdatabases is deprecated and included only for backward compatibility reasons. See the note at the top of the sys.sydatabases topic.
• IT researcher: Ok. sys.databases are views i guess. Does this gets effected by collation property too?
• Aaron Bertrand: Basically if anything is named sys<something> it's probably deprecated.

Answer 1

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases.

In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables:

SELECT c.name, c.collation_name
FROM sys.all_columns AS c
INNER JOIN sys.all_views AS v
ON c.[object_id] = v.[object_id]
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE s.name = N'sys' AND v.name = N'tables'
AND c.collation_name IS NOT NULL;

Results:

name SQL_Latin1_General_CP1_CI_AS
type Latin1_General_CI_AS_KS_WS
type_desc Latin1_General_CI_AS_KS_WS
lock_escalation_desc Latin1_General_CI_AS_KS_WS
durability_desc Latin1_General_CI_AS_KS_WS

So here we have columns with two different collations inside a single system object. No database can have a DATABASE_DEFAULT that matches both...

You don't have any control over the collation of your customer's columns or databases, or the server collation. So really the only ways to resolve the conflict are to:

• use a method that doesn't hard-code a specific collation (like DATABASE_DEFAULT)
• hard-code some specific, compatible collation on both sides

Since the latter is more work, makes for more complex queries, and introduces more opportunities to turn seeks into scans, I think the former is really your best option.

• IT researcher: I dont use custom databases and I use SQL_Latin1_General_CP1_CI_AS for my application.but if a customer has some other collation(sql server) then i will get error.So i think i always should take care of collation when query involves system databases.

Answer 2

From Books Online

database_default - Causes the COLLATE clause to inherit the collation of the current database.

If you are executing your second query from the common database then the value from master.dbo.sysdatabases is being coerced into common's collation, not the other way around as you suppose.

• IT researcher: Ok. But can u tell me should i use collate for all my queries which involves system databases?