Problem
In some scenarios we might find different SQL Server collations between the server instance and its databases. Sometimes the collation is fixed in some table columns or inside stored procedures to solve relationship problems with columns that have different collations. The collation differences may have been setup purposely, but in other cases they may have been a mistake. In this tip we will look at different ways to change the SQL Server collation for the instance and databases and things to be aware of when making these changes.
Solution
Before proceeding with making changes following the steps below, you should consider any issues that might occur by changing the collation either at the system level, database level or column level. In some cases things may have been setup purposely to handle specific needs, but this also may have been a mistake that was made during installation and you want to make sure all databases conform to your specifications. You should test these processes in your development environments before implementing in production.
Techniques that will be covered to change SQL Server collation
In this tip we will look at three different approaches and the outcome of each approach testing with SQL Server 2005 through 2019.
- Transact-SQL script
- Undocumented script
- Setup with SQL Server parameters
The purpose of this tip is to change all system databases and user databases to use the new collation setting. The different scenarios above will show what actually gets changed so you can determine which approach makes sense.
The techniques below that will be covered make the DBA’s daily work much easier, as well as provide a means to revert a misapplied configuration after the environment has been completely configured, but we always have some caveats to consider.
NOTE: We will make use of some of the available solutions, but problems may occur during their execution and may even cause some issues, so it is very important to note that these features should be tested prior to running in a production environment.
Environment Preparation for SQL Server Collation Change
In this first step we will simulate the situation with a new environment, we have the system databases and one user database created for testing purposes.
Once the environment is created and configured, as shown in the images below, we can view the Collation configured for both the instance, the system databases and user database.
Available Techniques for Collation Switching in SQL Server
At this point we will get to know the available techniques, understand them, and know the pros and cons of use, know which versions of SQL Server are compatible with each one and then apply them in our isolated environment for testing, but before that let’s get to know a bit more about Collation.
According to Microsoft documentation, we can get a glimpse through the quotes below from the following links:
Collation:
A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database. For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page.
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data that you are working with
When you select a collation for your server, database, column, or expression, you are assigning certain characteristics to your data that affects the results of many operations in the database. For example, when you construct a query by using ORDER BY, the sort order of your result set might depend on the collation that is applied to the database or dictated in a COLLATE clause at the expression level of the query.
Locale:
A locale is a set of information that is associated with a location or a culture. This can include the name and identifier of the spoken language, the script that is used to write the language, and cultural conventions. Collations can be associated with one or more locales.
Code Page:
A code page is an ordered set of characters of a given script in which a numeric index, or code point value, is associated with each character. A Windows code page is typically referred to as a character set or charset. Code pages are used to provide support for the character sets and keyboard layouts that are used by different Windows system locales.
Sort Order:
Sort order specifies how data values are sorted. This affects the results of data comparison. Data is sorted by using collations, and it can be optimized by using indexes.
Option # 1: Using Transact-SQL to Change SQL Server Collation
In this option we can use this approach to change collation for User Database and Columns.
Pros
- Easy collation change of user databases and their objects
Cons
- It is not possible to change the master database collation via T-SQL, for this we will see how to do that in scenarios 2 and 3.
- Changing the collation of the user database will not change the collation of the table columns belonging to the database. This has to be done column by column.
Step 1
Let’s confirm the current collation assigned to database Products:
SELECT name, collation_name FROM sys.databases WHERE name = 'Products';
Below we can see it is set to Latin1_General_CI_AS.
Step 2
Now let’s confirm the collation of the ProductGuid table columns:
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid'
We can see that the guidd column is also using the same collation Latin1_General_CI_AS.
Step 3
We can now redefine the desired collation for database Products with the following code:
-- make sure no one else is using database
ALTER DATABASE Products SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- change collation to Modern_Spanish_CI_AI_WS
ALTER DATABASE Products COLLATE Modern_Spanish_CI_AI_WS;
-- allow users back into the database
ALTER DATABASE Products SET MULTI_USER
Step 4
Let’s check again if the change was successful:
SELECT name, collation_name FROM sys.databases WHERE name = 'Products';
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid'
Below we can see that the database collation has changed, but the column in the table was not changed.
Change Column Collation
Note that column "guidd" in the ProductGuid table’s collation settings was not changed. To change the column collation, run the script below. This would be need to be done for each column in the database that you want to change.
ALTER TABLE ProductGuid ALTER COLUMN guidd nvarchar(200) COLLATE Modern_Spanish_CI_AI_WS
Now confirm the change.
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid'
Now we can see the change has been made for the column.
Testing Outcomes with Different SQL Server Versions
SQL Server versions used in this scenario and their results:
- Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86) Express Edition with Advanced Services. (Tested and successful)
- Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64) Express Edition with Advanced Services. (Tested and successful)
- Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2012 – 11.0.2100.60 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) – 13.0.1742.0 (X64) Developer Edition. (Tested and successful)
- Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2019 (CTP3.2) – 15.0.1800.32 (X64) Express Edition. (Tested and successful)
Conclusion
This approach is good to change specific user databases and columns.
Option # 2: Undocumented approach to Change SQL Server Collation
In this option we can use this approach to change collation for System Databases, User Database and Columns.
Keep in mind this is an undocumented solution and may not be supported by Microsoft if you have issues, so please test carefully before implementing in a production environment. Be aware that a successful operation cannot be guaranteed, but it is an option to try to solve the problem.
We may make use of this technique eventually in some cases, considering its pros and cons and being prepared for surprises that may occur.
This technique should be applied right after a fresh installation of SQL Server in order to modify the collation of both the instance and its databases and objects, but we may face some problems that we will need to understand, try to fix and repeat the process.
Pros
- Easy execution with few parameters.
- System databases collation change.
- User database collation change.
- Database objects collation change.
Cons
- Problems while running resulting in sometimes irreversible errors.
- Possible database and object corruption.
- If there are issues, Microsoft may not support you.
A simple way to use this technique is following a few steps that will help us to quickly solver our problem. Its recommended to create a backup of all databases (including system databases) before take administrative actions on the SQL Server instance.
Its important to ensure that there are no fixed collations inside stored procedures, triggers, etc, otherwise the command below may report problems.
Step 1
The server and master database have “Latin1_General_CI_AI” collation and we’ll change it to “SQL_Latin1_General_CP1_CI_AI” for the test.
The Products database and guidd column from ProductGuid table has “Modern_Spanish_CI_AI_WS” collation and we will confirm after execution that both objects have changed collation settings.
Let’s confirm the current SQL Server instance collation and all it databases including systems databases collation before taking actions.
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'
Below we can see the collation settings.
Step 2
We have to stop the SQL Server service for the changes to be applied. After stopping the SQL Server service, we have to open a command prompt with administrative privileges and go to the BINN directory of Microsoft SQL Server, following the example below:
This image below shows the SQL Server Binn directory and “sqlservr.exe” that will be used in this test.
Step 3
After the SQL Server service is stopped, execute the command below on the server.
The parameter “-s” is necessary only if you have a named instance.
sqlservr -m -T4022 -T3659 -s"SQL2017" -q"SQL_Latin1_General_CP1_CI_AI"
Parameters used:
[-m] single user admin mode
[-T] trace flag turned on at startup
[-s] sql server instance name
[-q] new collation to be applied
In this example, we used two trace flags:
- T4022: Trace Flag 4022: Bypass startup procedures in SQL Server.
- T3569: Trace Flag 3569: Enables logging all errors to errorlog during server startup.
A lot of information will appear, but no user action is required. When it is done, press Ctrl + C and confirm SQL Server shutdown.
Step 4
Start the SQL Server instance and confirm that all changes were applied on the server instance and all databases including the 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'
Below we can see the changes.
Testing Outcomes with Different SQL Server Versions
SQL Server versions used in this scenario and their results:
- Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86) Express Edition with Advanced Services. (Tested and successful)
- Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64) Express Edition with Advanced Services. (Tested and successful)
- Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2012 – 11.0.2100.60 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) – 13.0.1742.0 (X64) Developer Edition. (Tested and successful)
- Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2019 (CTP3.2) – 15.0.1800.32 (X64) Express Edition. (Tested and successful)
Conclusion
Since this is not a documented technique, it is difficult to seek help for operation errors during execution, but by using caution in an isolated environment, it is possible to validate if the process will occur without errors, so you can consider running in a production environment, of course, always ensuring that the backup of all databases is up to date.
Option # 3: Setup with SQL Server parameters to Change SQL Server Collation
In this option we can use this approach to change collation for System Databases, but it will reset the server back as if it were a new installation. The user databases will not be updated and they will not be attached after the process. Also, any data added to the system databases will be reset as well.
In this last option we will use the SQL Server setup via the command prompt and pass parameters to rebuild the system databases.
Before proceeding, you should backup all user databases, script out logins, script out SQL Agent Jobs, Operators, Alerts, etc. and script out server configuration settings.
If you just installed SQL Server and noticed that the collation is not correct, this is the best approach to take right after the installation to correct the collation settings.
After performing this procedure, you will need to restore or attach the user databases and run the scripts that you created above. To change the user database collation settings and column collation settings, follow Option #1 above.
Pros
- Much easier and faster to rebuild system databases
- Commands documented and supported by Microsoft
Cons
- No action on user databases, would have to use Option 1 to make these changes
- Possible issues connecting after execution
Step 1
Let’s first understand the command and parameters needed to use this scenario.
In these two links below, Microsoft provides additional information to understand the process:
Here are some remarks about rebuilding system databases found in the first link above which we will quote here:
- Limitations and Restrictions
- Prerequisites
Limitations and Restrictions
When the master, model, msdb, and tempdb system databases are rebuilt, the databases are dropped and re-created in their original location. If a new collation is specified in the rebuild statement, the system databases are created using that collation setting. Any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.
Prerequisites
Keep records about:
- All server-wide configuration values
- All service packs and hotfixes applied to the instance of SQL Server and the current collation and reapply these updates after rebuilding the system databases.
- Current location of all data and log files for the system databases
Scripts required to compose the prerequisites:
SELECT * FROM sys.configurations;
SELECT
SERVERPROPERTY('ProductVersion ') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('Collation') AS Collation;
SELECT name, physical_name AS current_file_location
FROM sys.master_files
WWHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));
Let’s look at the command that will be used:
We have many more documented parameters, but we will know just what is needed for what we need in this scenario:
- /QUIET or /Q: Specifies that Setup run without any user interface.
- /ACTION=REBUILDDATABASE: Specifies that Setup re-create the system databases.
- /INSTANCENAME=InstanceName: Is the name of the instance of SQL Server. For the default instance, enter MSSQLSERVER.
- /SQLSYSADMINACCOUNTS=accounts: Specifies the Windows groups or individual accounts to add to the sysadmin fixed server role.
- [ /SAPWD=StrongPassword ]: Specifies the password for the SQL Server sa account.
- [ /SQLCOLLATION=CollationName ]: : Specifies a new server-level collation (optional).
Step 2
First let’s confirm which collation we have configured for the instance and system databases:
Step 3
Let’s do the backup of database Products and detach before running the setup command.
Step 4
In this example we will change the collation settings to Modern_Spanish_CI_AI_WS with Windows Authentication mode:
The command will be:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQL2017 /SQLSYSADMINACCOUNTS=DESKTOP-2J2EKBE\wnd_rebuid / /SAPWD= wnd_auth_sql2017 /SQLCOLLATION=Modern_Spanish_CI_AI_WS
Step 5
Now we can confirm how things worked. We can see all system databases have been changed.
SQL Server versions used in this scenario and their results:
- Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86) Express Edition with Advanced Services. (Tested and Failed)
- Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64) Express Edition with Advanced Services. (Tested and Failed)
- Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2012 – 11.0.2100.60 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) – 13.0.1742.0 (X64) Developer Edition. (Tested and successful)
- Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64) Express Edition. (Tested and successful)
- Microsoft SQL Server 2019 (CTP3.2) – 15.0.1800.32 (X64) Express Edition. (Tested and successful)
Conclusion
We can see that system databases have had their collation changed, but this process does not change user database settings, but it is still a valid option to correct a collation configuration made in error. After this is done, you can attach or restore the databases and then follow the steps in Option 1 above.
Scripts to Check Collation Settings
Below are scripts you can use to check the collation settings for your instance, database and table columns.
-- check instance collation
SELECT convert(sysname, serverproperty(N'collation')) AS [Collation]
-- check current database collation
SELECT name, collation_name FROM sys.databases WHERE database_id = DB_ID()
-- check for table columns that do not match current database collation
SELECT DB_Name() as DatabaseName, SCHEMA_NAME(o.schema_id) as SchemaName, o.name as TableName, c.name as ColumnName, c.collation_name
FROM sys.objects o
INNER JOIN sys.columns c on o.object_id = c.object_id
WHERE o.is_ms_shipped = 0
AND collation_name is not null
AND collation_name not in (SELECT collation_name FROM sys.databases WHERE database_id = DB_ID())
-- if for some reason you get an error about collation differences for the column query you could try this
-- or something like this where you match the collations
SELECT DB_Name() as DatabaseName, SCHEMA_NAME(o.schema_id) as SchemaName, o.name as TableName, c.name as ColumnName, c.collation_name
FROM sys.objects o
INNER JOIN sys.columns c on o.object_id = c.object_id
WHERE o.is_ms_shipped = 0
AND collation_name is not null
AND collation_name COLLATE SQL_Latin1_General_CP1_CI_AS not in (SELECT collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.databases
WHERE database_id = DB_ID())
Next Steps
- Please check these tips below to explore more scenarios:
