How to change an Azure SQL Database Collation

By:   |   Comments (4)   |   Related: > Azure SQL Database


Problem

We created an Azure SQL database using the default collation that was available. Now, there is a requirement from the client to change the collation. Is it possible to change the collation of an Azure SQL database after it is created?

Solution

This tip assumes you already have an Azure SQL database. If you are new to Azure, try this link: https://azure.microsoft.com/en-us/free/ which will give you $200 USD credit for a month. Otherwise, logon to your Azure portal: https://portal.azure.com.

As per Microsoft, it is not really possible to change the collation of an Azure SQL database once it is created. You can refer to this Microsoft link where it mentions that “You cannot change the collation for an Azure SQL Database after it is created”. However, in this tip, we will perform a demo with the following steps:

  • Export the Azure SQL database to an on-premises server
  • Change the database collation on the on-premises server
  • Export the on-premises database
  • Finally import into the Azure server with the new collation

In the next tip, we will explore how to hack the BACPAC file after exporting the Azure SQL database and re-importing the BACPAC file with the new collation.

Azure SQL Database collation

Connect to the Azure SQL database, right click on the database to view the properties and collation.

Azure SQL DB Collation

Here, the Azure SQL database collation is: SQL_Latin1_General_CP1_CI_AS as shown. Now, let us try to change the collation of the database to Latin1_General_100_CS_AS_SC.

Run this code on the Azure SQL database to change the collation.

USE master;  
GO  
ALTER DATABASE SQLTips  
COLLATE Latin1_General_100_CS_AS_SC

This will generate an error message as shown.

Error on changing Azure SQL DB Collation

The error says - “The database could not be exclusively locked to perform the operation”. Sometimes, you may have a different error message, but no matter what the error is, you will not be able to change the collation of an Azure SQL database this way.

Export Azure SQL Database to a BACPAC

In order to restore the Azure database on to an on-premises server, export the Azure database to a BACPAC. Connect to the Azure SQL instance, right click on the Azure SQL database and click on the option to “Export Data-tier application”.

Right click on Azure SQL DB

Once you click on “Export Data-tier Application”, you will get this “Introduction” screen where the wizard describes in brief the process to follow. Click on “Next”.

Introduction_screen_wizard

In the next screen, you will get the option to specify the location of the BACPAC file. Provide a file name for the BACPAC file and click on “Next”.

Provide_location_of_BACPAC

In the next window, you will see the summary section.

Review_Summary_Export_BACPAC

Review the details on the screen and click on “Finish”. Once you click on “Finish”, the process of extracting the schema and data begins and this process takes some time to complete based on the size and contents of your Azure SQL database.

Progress_of_Export_BACPAC

Once the process completes, you will see the final confirmation of the process completion as shown.

Export_Process_Completes

With this, you have successfully exported the Azure SQL database to a BACPAC.

Import BACPAC file to an on-premises SQL Server instance

Connect to your on-premises SQL Server instance and start the process to import the BACPAC file. Connect to your SQL instance, right click on the “Databases” option to “Import Data-tier Application”.

Import Data-tier application On-Premise

You will see this “Introduction” screen, click on “Next”.

Import data-tier app introduction

Click on “Next” to provide the location of BACPAC file. This is the same file you exported from the Azure SQL database.

Import BACPAC file

Once you provide the actual BACPAC file location, click on “Next”. In the following window, provide the location for the data and log files to be restored on-premises.

Specify setting for import on premise

Once you click on “Next”, you will see the “Summary” section to review. Click on “Finish” to start the import process.

Summary_Import_BACPAC

Once you click on “Finish”, the process of Importing the database begins.

Import Data Tier Process starts

Once the process completes, you will get confirmation of completion as shown. Click on “Close”, once you see this screen.

Data-Tier Import_Process_Completes

Database restored on on-premises SQL Instance and change collation of the on-premises database

Once the import process completes, you will notice that a new database has been created on your on-premises SQL instance.

AzureSQLDB-Restored-On-Premise

Before you change the collation of this database, check the current collation of the “SQLTips” database on the on-premises instance.

Collation_of_On-premiseDB

The collation - “SQL_Latin1_General_CP1_CI_AS” is the same collation as that of the Azure SQL database. Connect to the on-premises SQL instance and run the command to change the database collation. Run this code on the on-premises SQL database to change the collation.

USE master;  
GO  
ALTER DATABASE SQLTips  
COLLATE Latin1_General_100_CS_AS_SC

You will notice that the command completed successfully.

Collation changed on premise database

You can check the collation of the on-premises SQLTips database to confirm that the collation has changed.

New collation of on-premise DB

This confirms that the collation of the on-premises database is changed to Latin1_General_100_CS_AS_SC

Export on-premises database to BACPAC

Now that you have the new collation for your on-premises SQL database, perform the step to export the data-tier application of the on-premises SQL database. Right click on the database, go to “Tasks”, and click as shown.

Export data-tier on-premise database

Just follow the sequence of steps in the wizard similar to what you had performed for exporting the Azure SQL database. The only difference now is you are exporting the on-premises SQL database with the new collation. The process completes in some time similar to the time taken for the exporting the Azure SQL database.

Import the BACPAC from on-premises database to the Azure SQL instance which hosts the Azure SQL database

With the BACPAC file exported from on-premises SQL instance, connect to the Azure SQL instance to import the BACPAC file.

Import-on-Azure-SQL-Instance

You will get the wizard, similar to what you had seen while importing the BACPAC to on-premises SQL instance earlier on. Just follow the sequence in the wizard to complete the import process. Ensure to select the correct BACPAC file while performing the import as shown.

Import correct BACPAC file on Azure SQL instance

Select the correct BACPAC file and click on “Next”, which will take you to this screen.

Azure SQL DB Auto selected

You will notice that a new database name gets auto populated. We wouldn’t be able to provide same name as that of the Azure SQL DB – “SQLTips” as it already exists. If you try renaming the database as “SQLTips”, you will get this error message.

Error due to same DB name

You can either leave the auto populated database name as the default or provide a name of your choice and click on “Next” to start the process of importing the BACPAC on to the Azure SQL instance. Once the import process completes, the new database would be available to use as shown.

New Azure SQL DB with new Collation

With this, we have the desired collation for the Azure SQL database. You may delete the “SQLTips” database with the old collation and rename the new “SQLTips_on_premises” database as “SQLTips” if you wish to retain only one database with the correct collation. With this workaround, we were able to change the collation of an Azure SQL database even though it is not possible to directly make the collation change after the Azure SQL database is created. In the next tip, we will try to hack the BACPAC file after it is exported from the Azure SQL database and make the collation update directly on the BACPAC file and import it back to the Azure SQL instance.

Next Steps
  • In this tip, you were able to apply a work around to change the collation of an Azure SQL database after it was created.
  • Try this tip on your own test Azure SQL database.
  • Try this tip using your own on-premises SQL Server instance.
  • To get familiar with Azure, refer the numerous tips available at this link.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

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




Wednesday, December 18, 2019 - 2:38:54 PM - Mohammed Back To Top (83472)

hi Robin,

That is correct, this is for changing database collation. For changing the column collation, you may manually try updating the desired columns.

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

If not, please refer this solution.

https://stackoverflow.com/questions/18122773/change-collations-of-all-columns-of-all-tables-in-sql-server

Thanks,

Mohammed

Wednesday, December 18, 2019 - 3:16:19 AM - Robin Back To Top (83463)

Hi Mohammed,

Great post! We found out that for our case we first had to set the database from multi-user to to single-user and after the collation change back to multi-user again. With that, we managed to change the collation of the database and the tables in the database, but the columns in the tables seem to still have the old collation. Any tips on how to manage that efficiently? Thank you in advance!

Kind regards,

Robin

Wednesday, October 2, 2019 - 4:45:57 PM - M Moinudheen Back To Top (82653)

hi Einar,

Yes, it is possible. Try using nVarchar for this purpose.

You would be able to insert the data and query without issues. Use this example.

First create the table

CREATE TABLE LANG

(
NAME VARCHAR(50),
TXT NVARCHAR(100)
)

Insert first without using N

INSERT INTO LANG
VALUES ('English','Hello World')
INSERT INTO LANG
VALUES ('Hindi','हैलो दुनिया')
INSERT INTO LANG
VALUES ('Chinese','你好世界')

You won't see proper output for the foreign languages.

Try again, using correct method and run a select, you should see the output correctly

INSERT INTO LANG
VALUES ('English',N'Hello World')
INSERT INTO LANG
VALUES ('Hindi',N'हैलो दुनिया')
INSERT INTO LANG
VALUES ('Chinese',N'你好世界')

The Azure SQL DB collation used was SQL_Latin1_General_CP1_CI_AS

Wednesday, October 2, 2019 - 11:18:39 AM - Einar Larusson Back To Top (82650)

I have one question, can you have in the same Azure SQL Server Database both Western and Asian data? An example would be a table of customers and the name could be written in the western way using the western aplhabet and the names for the Asian customers would be written in lets say Mandarin, Malay, Japaneese or any other Asian language and "Alphabet"














get free sql tips
agree to terms