SQL SERVER – Change Database and Table Collation

December 1, 2020

Recently I was helping my client on Comprehensive Database Performance Health Check and we walked into a very interesting scenario. We had to change the database collation. Let us learn how we can do that.

We will be creating a database name CollationTest with the collation SQL_Latin1_General_CP1_CI_AS and we will change the collation to SQL_Latin1_General_CP1_CS_AS .

First, let us check the collation of the database.

SELECT name, collation_name
FROM sys.databases
WHERE name = 'CollationTest';

Next, let us check the collation of the Test table created inside the CollationTest Database.

SELECT name, collation_name
FROM sys.all_columns
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable';

It is very clear from both the results that the collation used in the database and the column is SQL_Latin1_General_CP1_CI_AS.

Now let us change the collation of the database and column used in the database by running the following script.

ALTER DATABASE CollationTest 
	SET SINGLE_USER 
	WITH ROLLBACK IMMEDIATE;
ALTER DATABASE CollationTest 
	COLLATE SQL_Latin1_General_CP1_CS_AS;
ALTER TABLE TestTable 
	ALTER COLUMN Col1 NCHAR(10) 
	COLLATE SQL_Latin1_General_CP1_CS_AS;
ALTER DATABASE CollationTest 
	SET MULTI_USER;

The script above will change the collation of the database as well as for the column used in the database. In the real world, you will have many tables and many columns for each of them you will have to generate the script.

Now when you run the script to check the collation you will get the following results.

I believe lots of people have been searching for this on SQLAuthority.com blog hopefully, this blog post will be helpful. If you have any questions you can always reach out to me on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)