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:
- Set the database to single-user mode;
- Perform the necessary changes to your database, in this case change collation;
- 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:
- export old data from each table, either using SQL Server Import and Export data wizard or bcp;
- drop and recreate the tables; and
- 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
-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
hello, thanks for the article! …. a stupid question: do I understand correctly that this changes the collation of the existing database? sorry for my English
Yes, it updates also the existing data.