Changing SQL Server 2012 Database Sorting

Question

Change Sort

I need to change the sorting of one of our databases on a specific server from Latin1_General_CI_AS to SQL_Latin1_General_CP1_CI_AI so that it matches the rest of our databases.

Problem

However, when I try to do this, I get the following error:

Error ALTER DATABASE. The default value for the database "XxxxxXxxxxx" cannot be set to SQL_Latin1_General_CP1_CI_AI. (Microsoft SQL Server, Error: 5075)

My research

My search on the topic revealed a number of articles that indicate that I need to export all the data, delete the database, recreate it with the correct sort, and then re-import the data.

For example: Problem with changing database sorting (SQL Server 2008)

Obviously, this is an important task, especially since the relationship of the primary and foreign keys must be preserved, and our database is quite large (more than ten million data rows).

My question

Is there a way to change the collation of an existing SQL Server 2012 database that does not require exporting and re-importing all the data?

Alternatively, is there any tool or script (s) that can automate this process in a reliable way?

Answer 1

In SQL Server 2012, the following works for me:

 ALTER DATABASE CURRENT COLLATE SQL_Latin1_General_CP1_CI_AI; 

The accepted answer in a related question is not entirely correct, at least not for SQL Server 2012. It says:

Ahh, this is one of the worst problems in SQL Server: you cannot change the sort after creating the object (this is true for both tables and databases ...).

But I was just able to change the default setting, and I have populated tables. The MSDN page for ALTER DATABASE is contained in the Remarks section of the Change Database Sort section:

Before you apply another database to the database, make sure that the following conditions are set:

• You are currently using only the database.

• The object associated with the schema binding depends on the sorting of the database.

  If the following objects exist in the database that depend on the sorting of the database, the COLLATE statement of the ALTER DATABASE database will fail. SQL Server will return an error message for each object that blocks the ALTER action:

  • Custom functions and views created using SCHEMABINDING.

  • Computed Columns

  • CHECK Limitations.

  • Table functions that return tables with character columns with collaborations inherited from the default database sort.

So, I would suggest making sure that the database is in Single-User mode, and that if you have any of these four elements, you:

• drop them
• change sort
• and then re-add them

BUT, at this point, all that has been changed is the standard database sort. Accounting for all existing columns in user tables (i.e., Non-system tables) will still have the original collation. If you want the existing string columns - CHAR , VARCHAR , NCHAR , NVARCHAR and the obsolete TEXT and NTEXT - to take a new sort, you need to change each of these columns individually. And if there are any indexes in these columns, then these indexes will need to be discarded first (disconnecting is not enough) and created again after ALTER COLUMN (other dependencies that would prevent ALTER COLUMN have already been removed to make ALTER DATABASE work). The example below illustrates this behavior:

Test setup

 USE [tempdb]; SET NOCOUNT ON; CREATE TABLE dbo.ChangeCollationParent ( [ChangeCollationParentID] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_ChangeCollationParent] PRIMARY KEY, ExtendedASCIIString VARCHAR(50) COLLATE Latin1_General_CI_AS NULL, UnicodeString NVARCHAR(50) COLLATE Latin1_General_CI_AS NULL ); CREATE TABLE dbo.ChangeCollationChild ( [ChangeCollationChildID] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_ChangeCollationChild] PRIMARY KEY, [ChangeCollationParentID] INT NULL CONSTRAINT [FK_ChangeCollationChild_ChangeCollationParent] FOREIGN KEY REFERENCES dbo.ChangeCollationParent([ChangeCollationParentID]), ExtendedASCIIString VARCHAR(50) COLLATE Latin1_General_CI_AS NULL, UnicodeString NVARCHAR(50) COLLATE Latin1_General_CI_AS NULL ); INSERT INTO dbo.ChangeCollationParent ([ExtendedASCIIString], [UnicodeString]) VALUES ('test1' + CHAR(200), N'test1' + NCHAR(200)); INSERT INTO dbo.ChangeCollationParent ([ExtendedASCIIString], [UnicodeString]) VALUES ('test2' + CHAR(170), N'test2' + NCHAR(170)); INSERT INTO dbo.ChangeCollationChild ([ChangeCollationParentID], [ExtendedASCIIString], [UnicodeString]) VALUES (1, 'testA ' + CHAR(200), N'testA ' + NCHAR(200)); INSERT INTO dbo.ChangeCollationChild ([ChangeCollationParentID], [ExtendedASCIIString], [UnicodeString]) VALUES (1, 'testB ' + CHAR(170), N'testB ' + NCHAR(170)); SELECT * FROM dbo.ChangeCollationParent; SELECT * FROM dbo.ChangeCollationChild; 

Test 1: Collation Column Change Without Dependencies

 ALTER TABLE dbo.ChangeCollationParent ALTER COLUMN [ExtendedASCIIString] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL; ALTER TABLE dbo.ChangeCollationParent ALTER COLUMN [UnicodeString] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL; ALTER TABLE dbo.ChangeCollationChild ALTER COLUMN [ExtendedASCIIString] VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL; ALTER TABLE dbo.ChangeCollationChild ALTER COLUMN [UnicodeString] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL; SELECT * FROM dbo.ChangeCollationParent; SELECT * FROM dbo.ChangeCollationChild; 

The ALTER COLUMN statements described above have completed successfully.

Test 2: Change the Dependency Mapping column

 -- First, create an index: CREATE NONCLUSTERED INDEX [IX_ChangeCollationParent_ExtendedASCIIString] ON dbo.ChangeCollationParent ([ExtendedASCIIString] ASC); -- Next, change the Collation back to the original setting: ALTER TABLE dbo.ChangeCollationParent ALTER COLUMN [ExtendedASCIIString] VARCHAR(50) COLLATE Latin1_General_CI_AS NULL; 

This time, the ALTER COLUMN statement received the following error:

Msg 5074, Level 16, State 1, Line 60
The index "IX_ChangeCollationParent_ExtendedASCIIString" depends on the column "ExtendedASCIIString".
Msg 4922, Level 16, State 9, Line 60
ALTER TABLE ALTER COLUMN ExtendedASCIIString failed because one or more objects are accessing this column.

ALSO, note that the sorting of some string columns in the system catalog views with databases (e.g. sys.objects , sys.columns , sys.indexes , etc.) will change to the new Ordering. If your code has JOINs for any of these string columns (i.e. name ), you can start getting Collation mismatch errors until you change Collation on the join columns in your user tables.