How to change SQL Server collation

Question

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.

Answer 1

Yes.

You can change the default collation of SQL Server 2008 R2 express instance and individual databases, but it is a complex task.

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.

Answer 2

Be sure you really want to "drop" the user databases as noted in the answer above. You may just want to "detach" the databases. Or really, you can do nothing as rebuilding the master effectively removes any links to the user databases. There are times when the databases are created in the desired collation but the server isn't. You wouldn't want to have to recover all your user databases from backups in this case.

Answer 3

I did something like this and it worked but you have to keep in mind the indexes that are pointing to data type as text/varchar/nvarchar have to be dropped, run the script and then create the indexes.

USE YourDataBase
GO

DECLARE @Table_Name NVARCHAR(100)
SET @Table_Name = NULL--- THIS IS THE TableName that you want to change its collation columns

--- if null will set to all tables

DECLARE @TempTable AS TABLE
(
ID INT IDENTITY
,TableName NVARCHAR(100)
,ColumnName NVARCHAR(100)
,TypeName NVARCHAR(100)
,Max_length INT
,Collation_Name NVARCHAR(100)
,EnterDtm DATETIME DEFAULT GETDATE()
)
DECLARE @NewCollation NVARCHAR(100)
SET @NewCollation = 'Latin1_General_CI_AS' --- THIS IS THE COLLATION NAME THAT YOU WANT TO CHANGE

INSERT INTO @TempTable(TableName,ColumnName,TypeName,Max_length,Collation_Name)
SELECT 
QUOTENAME(SCHEMA_NAME(tables.schema_id)) + '.' + QUOTENAME(tables.name) AS TableName
,all_columns.name AS ColumnName
,type_name(all_columns.user_type_id)
,all_columns.max_length
,all_columns.collation_name  
from sys.all_columns INNER JOIN sys.tables ON
tables.object_id = all_columns.object_id
AND collation_name IS NOT NULL
AND all_columns.collation_name != @NewCollation
WHERE tables.object_id = ISNULL(object_id(@Table_Name),all_columns.object_id)


DECLARE @TableID SMALLINT
SET @TableID = (SELECT MIN(ID) FROM @TempTable)

DECLARE @Query NVARCHAR(1000),@TableName NVARCHAR(100),@ColumnName NVARCHAR(100),@TypeName NVARCHAR(100)
,@Size INT

WHILE @TableID IS NOT NULL
BEGIN
    SET @TableName = (SELECT TableName FROM @TempTable WHERE ID = @TableID)
    SET @ColumnName = (SELECT QUOTENAME(ColumnName) FROM @TempTable WHERE ID = @TableID)
    SET @TypeName = (SELECT TypeName FROM @TempTable WHERE ID = @TableID)
    SET @Size = (SELECT Max_length FROM @TempTable WHERE ID = @TableID) 

    SET @Query='ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @TypeName+ ISNULL ('(' +CAST(@Size AS VARCHAR(200))+')', '') +' COLLATE '+ @NewCollation  
    PRINT (@Query)

    SET @TableID = (SELECT MIN(ID) FROM @TempTable WHERE ID > @TableID)
END 

this is my first answer posted pardon my mess

Answer 4

Exporting all of the data (including logins, linked servers, SQL Agent jobs, DB Mail settings, etc), and rebuilding the instance-level data, plus reloading all of the user data, is a lot of work. And, even after all of that, there's still no guarantee that you can update a database's default collation via ALTER DATABASE because there are several conditions that will prevent the operation from completing (please see the "Changing the Database Collation" section of the ALTER DATABASE documentation for details).

There is, however, an undocumented method that is much easier. The main drawback being that it's unsupported. This is not to say that anything will go wrong, just that if something does, Microsoft won't help fix it (because they never guaranteed that it would work).

The method I speak of is running sqlservr.exe with the -q {new_collation_name} switch. There's a little more to it than that, but that is the basic idea. This method simply updates the system meta-data, which has benefits and consequences, the main ones being:

BENEFITS

• pretty fast
• bypass most restrictions that prevent ALTER DATABASE from working
• likely far more accurate than any script that people have come up with over the years to drop and recreate objects

DRAWBACKS

• unsupported if something goes wrong
• VARCHAR data can change, IF the code page is different between the old and new collations, and characters with values of 128 - 255 (0x80 - 0xFF) exists, and those characters do not exist as the same character with the same value on the new code page. So the potential is there for data loss, and your data needs to be research first to ensure that this condition does not exist. But, this also means that there are plenty of cases with only characters having values of 0 - 127 which are not in any danger, even if the code page changes.
• User-Defined Table Types (UDTTs) are skipped and need to be updated manually.

For a detailed description of what the sqlservr.exe -q method does and does not do (including details on how collations work at the various levels, and potential issues to watch out for), please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

To change only the instance (including the system databases: master, model, msdb, and tempdb) and one or more databases (but not all databases), simply detach the database(s) that you want to exclude from this operation, and then re-attach them once the collation update completes.