Sql-server – Are SQL Collations per Instance or per Database

sqlsql serversql-server-2008sql-server-2008-r2

I have a single SQL Instance, let's say MSSQLSRV – is the collation per Instance, i.e. every database in the given instance has the same collation, or are collations set per database? i.e. multiple collations per instance.

Best Answer

Per database. The instance level collation defines the default collation for the entire instance but you can set it independently at the database level if you want to override the default.

EDIT:
In response to your comment:

You can check the collation by looking at the database properties in SSMS (right-click the database, get properties, it's on the General page) or by executing this TSQL statement:

SELECT DATABASEPROPERTYEX('MyDatabase','Collation') AS DbCollation

(replace MyDatabase with the name of the database you want to check)

Related Solutions

Sql-server – How to change the collation of a SQL Server Database

MS KB 325335 has options on how to do this for the whole db and all columns.

Basically:

Script database tables (with new collation)
DTS/SSIS data (watching collation)
Add constraints

Sql-server – n easier way of resolving SQL Server/database collation mismatches than changing every column

One option is to "proof" your code against collation mismatch.

You can use the special collation "DATABASE_DEFAULT" to coerce without knowing what the actual collation is. You use it on char type columns in temp tables, table variables and system tables you need to use.

Example:

CREATE TABLE #Currency (CCY CHAR(3))
GO
INSERT #Currency VALUES ('GBP')
INSERT #Currency VALUES ('CHF')
INSERT #Currency VALUES ('EUR')
GO
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY --error!
GO
-- in join too
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY COLLATE DATABASE_DEFAULT --no error
GO
DROP TABLE #Currency
GO


CREATE TABLE  #Currency (CCY CHAR(3) COLLATE DATABASE_DEFAULT)
GO
INSERT #Currency VALUES ('GBP')
INSERT #Currency VALUES ('CHF')
INSERT #Currency VALUES ('EUR')
GO
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY --no error!
GO

DROP TABLE #Currency
GO

This also means that when your clients migrate their DB onto a new whizzy SQL Server box with yet another different collation, it works too...

Best Answer

Related Solutions

Sql-server – How to change the collation of a SQL Server Database

Sql-server – n easier way of resolving SQL Server/database collation mismatches than changing every column

Related Topic