CODEWITHSUNDEEP
sql-servercollation
Karl
Karl

Reputation: 3372

Please confirm behaviour of collate database_default in TempDB

I am uncertain of which collation is used when a temp table is created with 

create table #temp 
( object varchar(16) COLLATE database_default
, lev int )

E.g. does this use the TempDB collation or the user database collation?

Upvotes: 0

Views: 340

Answers (2)

Karl
Karl

Reputation: 3372

Based on Ben's answer I did the following. This makes it clear that the collation of the User database is used. 

/* Find Collation of SQL Server Database */

SELECT DATABASEPROPERTYEX('objective', 'Collation')
-- Latin1_General_BIN2

SELECT DATABASEPROPERTYEX('tempDB', 'Collation')
-- SQL_Latin1_General_CP1_CI_AS

GO

/* Find Collation of SQL Server Database Table Column */

USE objective
GO

create table #temp
( id int
, word varchar(10))

select collation_name
from tempdb.sys.columns
where OBJECT_ID = object_id('tempdb.dbo.#temp');
-- SQL_Latin1_General_CP1_CI_AS


create table #temp2
( id int
, word varchar(10) COLLATE database_default)

select collation_name
from tempdb.sys.columns
where OBJECT_ID = object_id('tempdb.dbo.#temp2');
-- Latin1_General_BIN2

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32707

It uses the collation of tempdb. I tested on my local machine by setting the collation for one of my user databases to Albanian_BIN (which, notably, is different than the collation for tempdb) and running:

create table #foo (a varchar(100), b nvarchar(100));
select collation_name
from tempdb.sys.columns
where OBJECT_ID = object_id('tempdb.dbo.#foo');

This came back with two rows, both of which reflected the collation of tempdb.

Upvotes: 0

Related Questions