How to set or change the SQL Server collation

Case #

You need to set the collation at either an SQL Server instance level or at a SQL Server database level. The collation can either be set at creation time or changed afterwards.

Solution #

Set or change the SQL collation at SQL Server instance level #

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.

Set or change the SQL collation at SQL Server database level #

Via the SQL Server Management Studio

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases.
2. If you are creating a new database, right-click Databases and then click New Database. If you do not want the default collation, click the Options page, and select a collation from the Collation drop-down list. Alternatively, if the database already exists, right-click the database that you want and click Properties. Click the Options page, and select a collation from the Collation drop-down list.
3. After you are finished, click OK.

Via T-SQL command line #

To set the database collation #

1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute. This example shows how to use the COLLATE clause to specify a collation name. The example creates the database MyOptionsTest that uses the Latin1_General_100_CS_AS_SC collation. After you create the database, execute the SELECT statement to verify the setting.

SQL

USE master;  
GO  
IF DB_ID (N'MyOptionsTest') IS NOT NULL  
DROP DATABASE MyOptionsTest;  
GO  
CREATE DATABASE MyOptionsTest  
COLLATE Latin1_General_100_CS_AS_SC;  
GO  
  
--Verify the collation setting.  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'MyOptionsTest';  
GO  

To change the database collation #

1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute. This example shows how to use the COLLATE clause in an ALTER DATABASE statement to change the collation name. Execute the SELECT statement to verify the change.

SQL

USE master;  
GO  
ALTER DATABASE MyOptionsTest  
COLLATE French_CI_AS ;  
GO  
  
--Verify the collation setting.  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'MyOptionsTest';  
GO  

Sources #

https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation?view=sql-server-ver15