Problem
During a recent database code review, we noticed the COLLATE DATABASE_DEFAULT clause in many of the stored procedures which join tables to temporary tables and linked servers. What does the COLLATE clause do and how can I learn about the behavior of COLLATE DATABASE_DEFAULT?
Solution
COLLATE is a clause applied to character string expression or column for textual data types such as char, varchar, text, nchar, nvarchar, and ntext to cast the string or column collation into a specified collation.
COLLATE can be specified with a specific collation name or use the COLLATE DATABASE_DEFAULT clause which will cast the character string expression or column collation into the collation of the database context where the command is executed.
All the T-SQL below was written and executed on a 64-bit version of SQL Server 2014 Enterprise Edition SP1. All the queries are executed in the same Query session due to the utilization of temporary tables.
Get the SQL Server instance collation
Below is the SQL Server instance collation. All the system databases such as master, tempdb, model and msdb will have the same collation as the SQL Server instance collation.
SET NOCOUNT ON
GO
SELECT SERVERPROPERTY('collation') SQLServerCollation
,DATABASEPROPERTYEX('master', 'Collation') AS MasterDBCollation
GOThe above returns the following output:
SQLServerCollation MasterDBCollation
---------------------------- -----------------------------
Latin1_General_CI_AS Latin1_General_CI_ASCreate a sample database, tables and data
Let’s create a user database which has a case sensitive collation as follows:
CREATE DATABASE [CaseSensitiveDB]
COLLATE Latin1_General_CS_AS_KS
GOCreate two permanent tables and two temporary tables and populate with sample records. The table CaseSensitiveDB.dbo.NonCSCollation will be created in the context of the [master] database with COLLATE DATABASE_DEFAULT. The other table PermTable and two temporary tables will be created in the context of the CaseSensitiveDB database.
Note that the first row inserted into PermTable is in uppercase, but otherwise all tables contain the same values.
USE [master]
GO
CREATE TABLE CaseSensitiveDB.dbo.NonCSCollation
( Value VARCHAR(100) COLLATE DATABASE_DEFAULT )
GO
USE [CaseSensitiveDB]
GO
CREATE TABLE dbo.PermTable
( Value VARCHAR(100) )
GO
CREATE TABLE #TableWithoutCollateDB
( Value VARCHAR(100) )
GO
CREATE TABLE #TableWithCollateDB
( Value VARCHAR(100) COLLATE DATABASE_DEFAULT )
GO
INSERT INTO dbo.PermTable (Value) VALUES ('RECORD 1'),('Record 2')
INSERT INTO #TableWithoutCollateDB (Value) VALUES ('Record 1'),('Record 2')
INSERT INTO #TableWithCollateDB (Value) VALUES ('Record 1'),('Record 2')Checking the column collation in NonCSCollation table, it will inherit the server collation even though the table is created in CaseSensitiveDB. The COLLATE DATABASE_DEFAULT clause will cast the table collation to the server collation which is Latin1_General_CI_AS.
USE CaseSensitiveDB
GO
sp_help NonCSCollation
GO
Examples to see differences with Collation
Query 1 below which joins to #TableWithoutCollateDB will fail with a collation conflict. This is because this temporary table is created using the Tempdb collation which is the same as the server collation while PermTable table uses the database collation.
-- Query 1
USE [CaseSensitiveDB]
GO
SET NOCOUNT ON
GO
SELECT COUNT(*)
FROM
[CaseSensitiveDB].dbo.PermTable PT
JOIN #TableWithoutCollateDB TA
ON PT.Value = TA.Value
GOWe get this error:
Msg 468, Level 16, State 9, Line 5
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CS_AS_KS" in the equal to operation.Query 2 will execute successfully because the column Value in temporary table #TableWithCollateDB will inherit the database collation and not the SQL Server instance collation. Both join columns are case sensitive hence the count would return a value of one.
-- Query 2
USE [CaseSensitiveDB]
GO
SET NOCOUNT ON
GO
SELECT COUNT(*)
FROM
[CaseSensitiveDB].dbo.PermTable PT
JOIN #TableWithCollateDB TB
ON PT.Value = TB.Value
GOResults:
-----------
1Assuming the query is written to join over a linked server or cross databases which have tables with different collation. Using COLLATE DATABASE_DEFAULT in the join on either or both sides of the column will set both columns to inherit the database context collation.
Using Query 1 which failed with a collation conflict as an example, there are three updated version of the query which will now execute successfully without collation conflict errors. All three queries will return row counts of one, because the database collation is case sensitive.
USE [CaseSensitiveDB]
GO
SET NOCOUNT ON
GO
-- Query 3
SELECT COUNT(*)
FROM
[CaseSensitiveDB].dbo.PermTable PT
JOIN #TableWithoutCollateDB TA
ON PT.Value COLLATE DATABASE_DEFAULT = TA.Value
GO
-- Query 4
SELECT COUNT(*)
FROM
[CaseSensitiveDB].dbo.PermTable PT
JOIN #TableWithoutCollateDB TA
ON PT.Value = TA.Value COLLATE DATABASE_DEFAULT
GO
-- Query 5
SELECT COUNT(*)
FROM
[CaseSensitiveDB].dbo.PermTable PT
JOIN #TableWithoutCollateDB TA
ON PT.Value COLLATE DATABASE_DEFAULT = TA.Value COLLATE DATABASE_DEFAULT
GOResults:
-----------
1
-----------
1
-----------
1Executing the same query in Step 5, but in the context of the master database. All queries now will inherit the server collation instead and the count will return two even though the PermTable table has case sensitive collation on the Value column.
USE [master]
GO
SET NOCOUNT ON
GO
-- Query 6
SELECT COUNT(*)
FROM
[CaseSensitiveDB].dbo.PermTable PT
JOIN #TableWithoutCollateDB TA
ON PT.Value COLLATE DATABASE_DEFAULT = TA.Value
GO
-- Query 7
SELECT COUNT(*)
FROM
[CaseSensitiveDB].dbo.PermTable PT
JOIN #TableWithoutCollateDB TA
ON PT.Value = TA.Value COLLATE DATABASE_DEFAULT
GO
-- Query 8
SELECT COUNT(*)
FROM
[CaseSensitiveDB].dbo.PermTable PT
JOIN #TableWithoutCollateDB TA
ON PT.Value COLLATE DATABASE_DEFAULT = TA.Value COLLATE DATABASE_DEFAULT
GOResults:
-----------
2
-----------
2
-----------
2The query below would also cause a collation conflict because of the differing collation.
-- Query 9
SELECT [Value] FROM [CaseSensitiveDB].dbo.PermTable
UNION
SELECT [Value] FROM #TableWithoutCollateDBAnd we get this error:
Msg 468, Level 16, State 9, Line 126
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CS_AS_KS" in the UNION operation.The COLLATE clause can also be used for columns of a SELECT query. The query would produce differing result depending on the context of the database.
-- Query 10
USE master
GO
SELECT [Value] AS TwoRows FROM [CaseSensitiveDB].dbo.PermTable
UNION
SELECT [Value] COLLATE DATABASE_DEFAULT FROM #TableWithoutCollateDB
-- Query 11
USE CaseSensitiveDB
GO
SELECT [Value] ThreeRows FROM [CaseSensitiveDB].dbo.PermTable
UNION
SELECT [Value] COLLATE DATABASE_DEFAULT FROM #TableWithoutCollateDBResults:
TwoRows
--------------------
RECORD 1
Record 2
ThreeRows
--------------------
Record 1
RECORD 1
Record 2Summary
The COLLATE DATABASE_DEFAULT clause provides flexibility in database development to resolve collation conflict issues assuming it is used appropriately. Writing an ad-hoc query with this clause may produce unexpected results if not well understood.
MSDN states COLLATE DATABASE_DEFAULT clause casts the collation of an expression, column definition, or database definition to inherit the collation of the “current database”. To complement MSDN, the “current database” is the context of the database where the query is executed.
Next Steps
