How to change SQL Server collation?
How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database?
Is there a way to do it using visual interface of SQL Server Management Studio? In the Server Properties window (and in the corresponding Database Properties window), this property is not available for editing. Pls guide me, how can i change SQL server collation
Yes.
You can change the default collation of SQL Server 2008 R2 express instance and individual databases, but it is a complex task.
For Changing the server collation in SQL Server
Export all your data using a tool such as the BCP Utility. For more information, see Bulk Import and Export of Data (SQL Server). Drop all the user databases. For more information, see Rebuild System Databases.
Sadly, there is no visual option to do it via SSMS.
SQL Server 2008 supports setting collations at the following levels:
- Server
- Database
- Column
- Expression
The default installation settings are determined by the Windows system locale. The server-level collation can either be changed during setup, or by changing the Windows system locale before installation. more...
Setting and Changing the Server Collation - SQL Server 2008
- Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
- Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.
- Drop all the user databases.
- Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command
- Create all the databases and all the objects in them.
- Import all your data.
- Setting and Changing the Database Collation - SQL Server 2008
- Set the COLLATION option in the CREATE DATABASE statement while creating a new database.
Similarly, set the COLLATION options in the ALTER DATABASE statement to change the collation of an existing database.
ALTER DATABASE [database_name] COLLATE SQL_Latin1_General_CP1_CI_AS;Setting and Changing the Column Collation
Some of the column collations will remain the same even after you alter the database collation. In that case, you have to modify the collation of the individual columns.