SQL SERVER – Change Database and Table Collation

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 .

SQL SERVER - Change Database and Table Collation collation0-800x220

First, let us check the collation of the database.

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

SQL SERVER - Change Database and Table Collation collation1

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';

SQL SERVER - Change Database and Table Collation collation2

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.

SQL SERVER - Change Database and Table Collation collation3

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)

SQL Collation, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Writing Infinite Loop
Next Post
SQL SERVER – Convert Formatted Integer Values into Date

Related Posts

Leave a Reply