Advertisements
Home > DBA Stuff > SQL DBA – Change a Database Collation in SQL Server

SQL DBA – Change a Database Collation in SQL Server


In previous post I discussed about collation differences b/w different databases/tables and how to query them. The solution was not permanent and every time one has to apply the default collation with the column names while matching them at where clause.

I thought to modify the collation of my database and tables on my new database and make it compatible to my old database. However this is not advisable for existing DBs, because collation setting within databases is done intentionally and for some purpose.

I issued the following sql statement to alter the database collation level:

alter database someDatabase collate SQL_Latin1_General_CP437_BIN

But it ended up on the following error:

Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'someDatabase' 
cannot be set to SQL_Latin1_General_CP437_BIN.

Then I came to know that the database has to be in single user mode, so that I can apply the changes. So I issued the following sql statements:

ALTER DATABASE someDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE someDatabase COLLATE SQL_Latin1_General_CP437_BIN
ALTER DATABASE someDatabase SET MULTI_USER

… and yes they get successfully executed. I checked the collation and it got changed to the new one.
But for the existing tables I had to manually change the collation of each & every tables by applying following sql statements:

alter table someTable alter column [someColumnX] varchar(9) COLLATE SQL_Latin1_General_CP437_BIN
Advertisements
  1. November 17, 2010 at 3:38 am

    As discussed in MSDN forum: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/14e46e5f-58ed-4c13-84cd-92b20727fbe9

    It is advised to rebuild tables and load data from old tables.

  1. March 4, 2016 at 5:04 pm
  2. July 7, 2015 at 2:30 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: