By: Simon Liew | 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
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
