SQL SERVER – Change Collation of Database Column – T-SQL Script

Just a day before I wrote about SQL SERVER – Find Collation of Database and Table Column Using T-SQL and I have received some good comments and one particular question was about how to change collation of database. It is quite simple do so.

Let us see following example.

USE AdventureWorks
GO
/* Create Test Table */
CREATE TABLE TestTable (FirstCol VARCHAR(10))
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Database Cleanup */
DROP TABLE TestTable
GO

When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.

Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.

Additionally, if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit here.

Reference : Pinal Dave (http://www.SQLAuthority.com)