The database collation level is set at CREATE DATABASE. If no collation level is set , the server collation is used.

Once the database is created , a requirement may arise to change the database collation. Use ALTER DATABASE to adjust the database collation level.

To change a collation level of an existing database use the following example as a basis .

--identify the current collation level

SELECT DATABASEPROPERTYEX('','collation');

--result

SQL_Latin1_General_CP850_CI_AS

GO

--alter the collation level

ALTER DATABASE 

COLLATE  Latin1_general_CI_AS;

GO

--check the collation level after the ALTER DATABASE

SELECT DATABASEPROPERTYEX('','collation');

--result

Latin1_General_CI_AS

NOTE:

1)  The  ALTER DATABASE <my_db> COLLATE requires an exclusive lock.

2) To obtain an exclusive lock do:

ALTER DATABASE <my_db> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

This forces all immediate transactions to be rolled back and all other connections in the database are disconnected.

To return to multi user state :

ALTER DATABASE <my_db> SET MULTI_USER;

3)  Always BACKUP the database before an important change.

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Author: Jack Vamvas (http://www.sqlserver-dba.com)