How to change the database collation

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

How to change the database collation

Posted on 2 January 2012
Comments
Briefcase
Print

To change the collation of an database following commands can be used:

ALTER DATABASE [database name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [database name] COLLATE Newcollation

ALTER DATABASE [database name] SET MULTI_USER

For example , suppose if the name of your database is learningdb and you want to change the collation to the collation SQL_Latin1_General_CP1257_CI_AS, then following commands can be used:-

ALTER DATABASE learningdb SET SINGLE_USER WITH ROLLBACKIMMEDIATE

ALTER DATABASE learningdb COLLATE SQL_Latin1_General_CP1257_CI_AS

ALTER DATABASE learningdb SET MULTI_USER