Set or Change the Server Collation

APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. You should carefully choose server-level collation because it affects:

  • Sorting and comparison rules in =, JOIN, ORDER BY and other operators that compare textual data.
  • Collation of the CHAR, VARCHAR, NCHAR, and NVARCHAR columns in system views, system functions, and the objects in TempDB (for example, temporary tables).
  • Names of the variables, cursors, and GOTO labels. Variables @pi and @PI are considered as different variables if the server-level collation is case-sensitive, and the same variables if the server-level collation is case-insensitive.

Setting the server collation in SQL Server

The server collation is specified during SQL Server installation. Default server-level collation is SQL_Latin1_General_CP1_CI_AS. Unicode-only collations cannot be specified as the server-level collation. For more information, see Collation and Unicode Support.

Changing the server collation in SQL Server

Changing the default collation for an instance of SQL Server can be a complex operation and involves the following steps:

  • Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

  • Export all your data using a tool such as the bcp Utility. For more information, see Bulk Import and Export of Data (SQL Server).

  • Drop all the user databases.

  • Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
    /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
    /SQLCOLLATION=CollationName  
    

    For more information, see Rebuild System Databases.

  • Create all the databases and all the objects in them.

  • Import all your data.

Note

Instead of changing the default collation of an instance of SQL Server, you can specify a default collation for each new database you create.

Setting the server collation in Managed Instance

Server-level collation (Preview) in Azure SQL Managed Instance can be specified when the instance is created and cannot be changed later. You can set server-level collation via Azure portal or PowerShell and Resource Manager template while you are creating the instance. Default server-level collation is SQL_Latin1_General_CP1_CI_AS. Unicode-only and new UTF-8 collations cannot be specified as server-level collation. If you are migrating databases from SQL Server to Managed Instance, check the server collation in the source SQL Server using SERVERPROPERTY(N'Collation') function and create a Managed Instance that matches the collation of your SQL Server. Migrating a database from SQL Server to Managed Instance with the server-level collations that are not matched might cause several unexpected errors in the queries. You cannot change the server-level collation on the existing Managed Instance.

See Also

Collation and Unicode Support
Set or Change the Database Collation
Set or Change the Column Collation
Rebuild System Databases