Change SQL Server Database Collation

A time may come when you will need to alter your database to use a different collation.

You can do this by using the following snippet:

USE master;
GO
ALTER DATABASE [DatabaseName]
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'[DatabaseName]';
GO

When you execute the above snippet you may come across the following error: [box type=”error” width=”100%” ]The database could not be exclusively locked to perform the operation[/box]

This error occurs when your database is in Multi-User mode or when there are objects referring to your database.

To overcome this error, you need to perform the following steps:

1. Set the database to single-user mode;
2. Perform the necessary changes to your database, in this case change collation;
3. Set the database back to multi-user mode.

So, the above snippet can be updated to the following one:

USE master;
GO

-- Set to single-user mode
ALTER DATABASE [DatabaseName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
  
-- change collation
ALTER DATABASE [DatabaseName]  
COLLATE SQL_Latin1_General_CP1_CI_AS;  
GO  

-- Set to multi-user mode
ALTER DATABASE [DatabaseName]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO  

--Verify the collation setting.  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'MyOptionsTest';  
GO

The above process can be used if you have just created a new database, with no data inside, without setting the desired database collation.

Change Collation to Table Columns

In your database there may be table columns with different collation, namely table columns of types char, varchar, text, nchar, nvarchar, and ntext. To list all table columns with their current collations, you may run the following query:

SELECT
  t.name,
  c.name,
  c.collation_name
FROM sys.columns c INNER JOIN sys.tables t
    ON t.object_id = c.object_id
WHERE c.object_id
      IN (SELECT object_id
          FROM sys.objects
          WHERE type = 'U')
      AND c.collation_name != 'NULL'
ORDER BY t.name, c.name

Before making any changes to these columns’ collations, you need to check whether the changes will generate any problems to the applications.

To alter a column collation to a new one, you may run the following query:

ALTER TABLE [<Table>] ALTER COLUMN [<Column>] <ColumnType>
  COLLATE <NewCollation>

In case of an error, make sure that the referencing constraints and indexes are dropped; you may recreate those after the collation has been changed.

Change Collation and Existing Data

What about your existing data? In order to be sure that existing data is retrievable after changing collation, you need to follow the steps below:

1. export old data from each table, either using SQL Server Import and Export data wizard or bcp;
2. drop and recreate the tables; and
3. import previously exported data.

In the following example we will create a database with collation LATIN1_GENERAL_CI_AI, add a table Products with a column Name (nvarchar(50)) and insert some data in Greek.

CREATE DATABASE [CollationTest] COLLATE LATIN1_GENERAL_CI_AI;
-- 

-- Create Products table and add some data
USE [CollationTest]
GO

CREATE TABLE [dbo].[Products](	
	[Name] [nvarchar](50) NULL
) ON [PRIMARY]
GO

insert into dbo.Products (name) values (N'Προϊόν 1');
insert into dbo.Products (name) values (N'Προϊόν 2');
insert into dbo.Products (name) values (N'Προϊόν 3');
insert into dbo.Products (name) values (N'Προϊόν 4');
insert into dbo.Products (name) values (N'Προϊόν 5');

Looking for all products starting with “Προϊόν” (Greek word for Product), we get 5 results:

SELECT *
FROM dbo.Products
WHERE Name LIKE N'Προϊόν%';

-- Results:
Name
--------------------------------------------------
Προϊόν 1
Προϊόν 2
Προϊόν 3
Προϊόν 4
Προϊόν 5

Step 1. Export Data

Let’s export data in Products table using bcp, which stands for bulk copy program utility (bcp) and bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.  This utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into adata file. To do so, we need the following command:

bcp CollationTest.dbo.Products out Products.txt -c -T

where -c performs the operation using a character data type and -T specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security.

Step 2. Change Database Collation

In the next step we will change database collation to GREEK_CI_AI, with the following statement:

-- Alter database collation
ALTER DATABASE [CollationTest] COLLATE GREEK_CI_AI;

Step 3. Drop and Recreate the Products table

USE [CollationTest]
GO

DROP TABLE [dbo].[Products];
GO

CREATE TABLE [dbo].[Products](	
	[Name] [nvarchar](50) NULL
) ON [PRIMARY]
GO

Step 4. Import previously exported data

To do so, we will use again bcp utility but instead of the `out` parameter we will use the `in` parameter:

bcp CollationTest.dbo.Products in Products.txt -c -T