Home / server / Questions / 523462
Accepted
PnP
Asked: 2013-07-16 10:02:44 +0800 CST

Are SQL Collations per Instance or per Database?

  • 772

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.

sql-server

2 Answers

  1. 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)

    • 4

  2. If you wish to list the collation of each database in an instance, try this:

    SELECT d.name as DbName, DATABASEPROPERTYEX(d.name,'Collation') AS DbCollation
FROM sys.databases d
WHERE d.database_id > 4
    • 0