When working with SQL Server databases, you may come across situations where you need to change the collation of a database or specific columns within a database. In this blog post, we will explore how to change the collation in SQL Server.

Let’s start by creating a database called “CollationTest” with the collation “SQL_Latin1_General_CP1_CI_AS”. To check the collation of the database, we can use the following query:

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

Next, let’s check the collation of a table called “TestTable” within the “CollationTest” database. We can use the following query:

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

From the results of both queries, we can see that the collation used in the database and the column is “SQL_Latin1_General_CP1_CI_AS”.

To change the collation of the database and the column, we can use 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 the column used in the database. In real-world scenarios, you may have multiple tables and columns, and you will need to generate the script accordingly.

After running the script, you can verify the collation by executing the previous queries again. You should now see that the collation has been changed to “SQL_Latin1_General_CP1_CS_AS”.

I hope this blog post has been helpful in understanding how to change collation in SQL Server. If you have any questions, feel free to reach out to me on LinkedIn.