Understanding the COLLATE DATABASE_DEFAULT clause in SQL Server

By: Simon Liew   |   Updated: 2016-08-03   |   Comments (7)   |   Related: > Database Configurations

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
GO

The above returns the following output:

SQLServerCollation           MasterDBCollation
---------------------------- -----------------------------
Latin1_General_CI_AS         Latin1_General_CI_AS

Create 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
GO

Create 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
GO

We get this error:

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
GO

Results:

-----------
1

Assuming 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 
GO

Results:

-----------
1

-----------
1

-----------
1

Executing 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 
GO

Results:

-----------
2

-----------
2

-----------
2

The 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 #TableWithoutCollateDB

And we get this error:

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 #TableWithoutCollateDB

Results:

TwoRows
--------------------
RECORD 1
Record 2

ThreeRows
--------------------
Record 1
RECORD 1
Record 2

Summary

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

• Learn more about COLLATE (Transact-SQL)
• Read these related tips
  • Changing SQL Server Collation After Installation
  • How column COLLATION can affect SQL Server query performance
  • How to change server level collation for a SQL Server Instance
  • How to Change the COLLATION of a SQL Server Column

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips