SQL Server – Why is There a Collation Conflict Between System Views

collationsql server

I'm using the following statement to retrieve a list of triggers from the database:

select tr.name
from sys.triggers tr
union all
select tr.name
from sys.server_triggers tr;

However on my local SQL Server 2012 Express installation this fails with the error:

Cannot resolve collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in UNION ALL operator occurring in SELECT statement column 1

I can "fix" this by changing the statement to explicitely use a different collation in the second part of the union:

select tr.name
from sys.triggers tr
union all
select tr.name collate database_default as name
from sys.server_triggers tr;

However I feel this is an ugly hack, and I don't really understand why there is a conflict in the first place. Both views ultimately retrieve the name from sys.sysschobjs.name and thus all values should have the same collation.

So my two questions are:

why is there a collation conflict in the first place?
is it safe to use collate database_default only in the second part, or should I better use it for both queries (performance is not an issue for this).

Interesting enough the same query does not create a problem when using it against a SQL Server 2005 (standard edition) with the same triggers defined.

Best Answer

sys.server_triggers is a server view, so it has the server collation. sys.triggers is a database view, so it has the database collation. You have a DB that has a collation different from the server. QED. Repro:

create database foo collate Latin1_General_CI_AI

use foo

select tr.name
from sys.triggers tr
union all
select tr.name
from sys.server_triggers tr;

Related Solutions

Sql-server – SQL Server: Collation of the resource database

The database Collation for the first SELECT statement in my query is Latin1_General_CI_AS.

Well, not exactly. There are a few problems with this statement:

The Database's default Collation only matters in a query when using string literals, variables, and return values from UDFs. AND, that default Collation only matters if there is no column or COLLATE keyword being used.
SELECT statements / queries, as a whole, do not use Collations. Collation is assigned per each string field, and it can be different for each field in a query.
The column in the first / top query, stopword, is not using the Latin1_General_CI_AS Collation (more on this in a moment).

I see that the system-stopwords are stored in the resource database, which can explain the difference in collation.

A column (or expression) coming from a different Database does not necessarily explain a difference in Collation. As stated above, Collation is set per each field of a query, whether that field comes from a column in a table or is an expression. Collation is usually derived naturally from Collation Precedence: Column Collation overrides literals and variables, and the COLLATE keyword overrides both. When there is a conflict, then you need the COLLATE keyword.

The main point here, however, is that if the stopword column in sys.fulltext_system_stopwords comes from a column in a Table in the Resource Database (i.e. mssqlsystemresource), OR if it comes from an expression in a View in the Resource Database that has its Collation set via the COLLATE keyword, then the default Collation of the Resource Database is irrelevant.

I can 'solve' my error by using COLLATE in my query

Yes, the COLLATE keyword is the way to go. However, to fix this conflict, you only need to specify the COLLATE keyword in one query, though it does not matter which query.

For example, if I run the query with no COLLATE keyword, I get:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw

UNION

SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the UNION operation.

So, assuming that the conflict is in the second (i.e. bottom) SELECT statement, I could fix it by applying the current DB's default Collation:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.fulltext_system_stopwords ftssw;

And that works. BUT, what if we try putting COLLATE on the first / top SELECT:

SELECT ftsw.stopword COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

That also works. And in fact, both of the following also work:

SELECT ftsw.stopword COLLATE Hebrew_100_CI_AS
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

-- and:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword COLLATE Hebrew_100_CI_AS
FROM sys.fulltext_system_stopwords ftssw;

Also, rather than use the DATABASE_DEFAULT option, which here equates to Latin1_General_CI_AS, I would use Latin1_General_BIN (or better yet: Latin1_General_100_BIN2, which is newer and better) in this particular case because it will ensure that different strings that can be normalized into the same string via the "distinct" behavior of the UNION (without the ALL) and the case-insensitivity of the Latin1_General_CI_AS Collation show up as different rows.

Where does the Latin1_General_BIN collation come from? It looks like the sys.fulltext_system_stopwords table has a different collation, but why?

That Collation comes from an unlikely source. Let's look at the system catalog views in the query:

EXEC sys.sp_help N'sys.fulltext_stopwords';

The results indicate that this is a View (as expected) and that the stopword column has a Collation of Latin1_General_BIN (not expected). But wait, if the Latin1_General_BIN Collation is coming from sys.fulltext_stopwords, then what about sys.fulltext_system_stopwords and where is the other Collation coming from? Let's look:

EXEC sys.sp_help N'sys.fulltext_system_stopwords';

The results indicate that this is a View (as expected) and that the stopword column has a Collation of SQL_Latin1_General_CP1_CI_AS (not expected).

We can now dig a little deeper into the definition of each of those system catalog views:

EXEC sys.sp_helptext N'sys.fulltext_stopwords';

Returns (simplified):

SELECT 
    fts.stopword,
FROM sys.sysftstops fts

And then:

EXEC sys.sp_help N'sys.sysftstops';

The results indicate that this is a system table, and that the stopword column indeed has a Collation of Latin1_General_BIN.

Next we can move on to the other system catalog view:

EXEC sys.sp_helptext N'sys.fulltext_system_stopwords';

Returns (simplified):

SELECT convert(nvarchar(64), stopword) as stopword, language_id
FROM OpenRowset(TABLE FTSYSSTPWD)

And FTSYSSTPWD comes from the Resource Database, so there isn't much more we can do at this point.

Still, there is one last thing we can do to be clear about the Collation of the data coming from the Resource Database -- sys.fulltext_system_stopwords.stopword:

CREATE DATABASE [FullTextCollationTest] COLLATE SQL_EBCDIC277_CP1_CS_AS;
GO
USE [FullTextCollationTest];

EXEC sys.sp_help N'sys.fulltext_stopwords';
-- Collation for [stopword] column: Latin1_General_BIN      (same as before)

EXEC sys.sp_help N'sys.fulltext_system_stopwords';
-- Collation for [stopword] column: SQL_EBCDIC277_CP1_CS_AS (same as DB's default Collation)

GO
USE [master];
DROP DATABASE [FullTextCollationTest];

SQL Server Collation – Using COLLATE: When to Use and When Not to Use

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.

Best Answer

Related Solutions

Sql-server – SQL Server: Collation of the resource database

SQL Server Collation – Using COLLATE: When to Use and When Not to Use

Related Question