In some scenarios, you may encounter different SQL Server collations between the server instance and its databases. This can lead to relationship problems with columns that have different collations. In this blog post, we will explore different ways to change the SQL Server collation for the instance and databases, and discuss important considerations when making these changes.

Techniques for Changing SQL Server Collation

There are three different approaches we will cover:

1. Using Transact-SQL script
2. Undocumented script
3. Setup with SQL Server parameters

1. Using Transact-SQL to Change SQL Server Collation

This approach allows you to change the collation for user databases and columns. However, it is not possible to change the collation of the master database via T-SQL. To change the collation of a user database, you need to alter the database and then change the collation of each column individually.

Here is an example of how to change the collation of a user database:

-- Confirm the current collation of the database
SELECT name, collation_name FROM sys.databases WHERE name = 'Products';

-- Confirm the collation of the table columns
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid';

-- Change the collation of the database
ALTER DATABASE Products SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Products COLLATE Modern_Spanish_CI_AI_WS;
ALTER DATABASE Products SET MULTI_USER;

-- Change the collation of a column
ALTER TABLE ProductGuid ALTER COLUMN guidd nvarchar(200) COLLATE Modern_Spanish_CI_AI_WS;

It is important to note that changing the collation of a user database will not automatically change the collation of the table columns belonging to the database. This needs to be done column by column.

2. Undocumented Approach to Change SQL Server Collation

This approach allows you to change the collation for system databases, user databases, and columns. However, it is an undocumented solution and may not be supported by Microsoft. It is recommended to test this approach carefully before implementing it in a production environment.

Here is an example of how to change the collation using the undocumented approach:

-- Confirm the current collation of the instance and system databases
SELECT CONVERT(varchar, SERVERPROPERTY('collation')) AS 'Server Collation';
SELECT name, collation_name FROM sys.databases WHERE name = 'master';
SELECT name, collation_name FROM sys.databases WHERE name = 'Products';
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid';

-- Stop the SQL Server service
-- Open a command prompt with administrative privileges and navigate to the SQL Server BINN directory
-- Execute the following command
sqlservr -m -T4022 -T3659 -s"SQL2017" -q"SQL_Latin1_General_CP1_CI_AI" 

-- Start the SQL Server service and confirm the changes
SELECT CONVERT(varchar, SERVERPROPERTY('collation')) AS 'Server Collation';
SELECT name, collation_name FROM sys.databases WHERE name = 'master';
SELECT name, collation_name FROM sys.databases WHERE name = 'Products';
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid';

3. Setup with SQL Server Parameters to Change SQL Server Collation

This approach allows you to change the collation for system databases, but it will reset the server back as if it were a new installation. User databases will not be updated or attached after the process, and any data added to the system databases will be reset as well.

Here is an example of how to change the collation using the setup with SQL Server parameters:

-- Confirm the current collation of the instance and system databases
SELECT CONVERT(varchar, SERVERPROPERTY('collation')) AS 'Server Collation';
SELECT name, collation_name FROM sys.databases WHERE name = 'master';

-- Backup the user databases and detach them
-- Run the SQL Server setup command with the necessary parameters
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQL2017 /SQLSYSADMINACCOUNTS=DESKTOP-2J2EKBE\wnd_rebuid /SAPWD=wnd_auth_sql2017 /SQLCOLLATION=Modern_Spanish_CI_AI_WS

-- Confirm the changes
SELECT CONVERT(varchar, SERVERPROPERTY('collation')) AS 'Server Collation';
SELECT name, collation_name FROM sys.databases WHERE name = 'master';

Conclusion

Changing the collation of SQL Server can be a complex task, but with the right techniques and considerations, it can be done successfully. It is important to test these processes in a development environment before implementing them in a production environment. Each approach has its pros and cons, so choose the one that best suits your needs and always ensure that you have backups of all databases before making any changes.

By following the techniques outlined in this blog post, you can effectively change the collation of your SQL Server instance and databases, ensuring that they conform to your specifications.