Change collation in SQL Server 2005/2008

Collation: It’s nothing but the set of rules that defined how data is stored and compared.

SQL Server default uses collation “SQL_Latin1_General_CP1_CI_AS” which is:

--The ISO code page 1252.

--The dictionary order, case-insensitive character sort order.

--The General Unicode collation

If you want to change this default collation to your own regional collation for your database, you can use below given script.

Alter Database AdventureWorks --give your database name

Collate SQL_LATIN_GENERAL1_CI_AS --give collation you want to apply

 

You may face the error while running above script sometime if your database is in multi user mode, in that case you have to set your database to single user mode, change collation and set your database to multi user mode. Look at the script below.

 Alter Database AdventureWOrks set single_user

Alter Database AdventureWorks --give your database name

Collate SQL_LATIN_GENERAL1_CI_AS --give collation you want to apply

Alter Database AdventureWOrks set multi_user

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com