How to change SQL Server Collation on Linux

By:   |   Comments   |   Related: > SQL Server on Linux


Microsoft released SQL Server to run on Linux servers and some of the administration tasks on Linux are different than on Windows.  One thing you may need to do is change the SQL Server collation and in this tip we walk through how this can be done for SQL Server on Linux.


Collations are used to specify the rules for how character strings are sorted and compared. This can make a difference on the behavior of queries and therefore there may be a need to change which collation is used.

In previous tips we explored Changing SQL Server Collation After Installation and How to change server level collation for a SQL Server Instance for SQL Server running on Windows and now we will look at how to do this for SQL Server on Linux.

To start with, we will use an installation of SQL Server on Red Hat.  You can read this tip Installing SQL Server vNext CTP1 on Red Hat Linux 7.2 to install SQL Server.

Once we have installed SQL Server on Linux, let's check the SQL Server version as shown below.

SQL Server Version

When installing SQL Server on Linux we don't get an option to select the instance collation, it is installed using the default value.

Let's check the SQL Server instance collation.

select convert(varchar,SERVERPROPERTY('Collation')

SQL Server Version

We can see the SQL Server collation is SQL_Latin1_General_CP1_CI_AS. Now suppose we want to change the SQL Server collation to Latin1_General_CI_AS. Follow the steps below.

Step 1 - Stop the SQL Server Services

First stop the SQL Server services using the below command.

sudo systemctl stop mssql-server

If we don't stop SQL Server services first and try to make the change we will get the below warning message.

SQL Server Version

Step 2 - Change the SQL Server Collation using mssql-conf

Run the "set-collation" option and follow the prompts to enter the collation.

sudo /opt/mssql/bin/mssql-conf set-collation

We will use Latin1_General_CI_AS as follows.

SQL Server collation change

The mssql-conf utility will try to change the databases using the specified collation and restart the service. If there are any errors, it will roll back the collation to the previous value.

SQL Server collation change

Once the change is successful, you will see the information below.

SQL Server collation change

Step 3 - Start the SQL Server services and verify collation

--Start SQL Services
sudo systemctl start mssql-server

--Verify SQL Server Collation after change
select convert(varchar,SERVERPROPERTY('Collation')

As we can see that the SQL Server collation is changed successfully to the desired collation.

SQL Server collation change
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms