SQL Server Database: Replacing Collation

  • Article

Introduction

This article is based on a question posted on the TechNet Forum Brazil for SQL Server - Replacing the Collate of a SQL Server Database and also influenced by the big need for clarification about Collate posts on the TechNet Forum International for SQL Server. This is a very common problem on the migrating of data through T-SQL and other products that use the features of SQL Server, such as: SharePoint and System Center.

This is one of the possible solutions related to this problem. If you know other options in SQL Server that meet the needs of this problem, feel free to add your content to this article.

Problem

During my reading of the threads in the SQL forum, I found the following question that was discussed.

The question was: "I would like to change the collation of a particular database, but I would ask if change a collation of a database must also apply in the columns of existing tables? Exist any process that modifies all the columns or I have to manually apply 'column to column'?"

It's clear that the person who asked the question, had changed the Collation of the database to SQL_Latin1_General_CP1_CI_AI, and went on to receive several errors in multiples statements of change tables, especially where columns of varchar datatype were part of the conditions in a JOIN statement.

When he tried to change the Collation of the columns in a table, the following error occurred:

ALTER TABLE BibliografiaTipos
  ALTER COLUMN Descricao  varchar(50)  COLLATE SQL_Latin1_General_CP1_CI_AI
GO

--OUTPUT WINDOW

Msg 4902, Level 16, State 1, Line 4
Cannot find the object "BibliografiaTipos" because it does not exist or you do not have permissions.

A second error happened when the proponent of the question tried to changing others tables with varchar columns that had relationship between the database tables, and also varchar columns as Primary Key.

Msg 5074, Level 16, State 1, Line 5
The object ‘FK_Frequences_STUDENT’ is dependent on column ‘Student_RA’.
Msg 4922, Level 16, State 9, Line 5
ALTER TABLE Frequences_STUDENT ALTER COLUMN Student_RA failed because one or more objects access this column.

Have varchar columns as the primary key made ​​all the difference for the solution of the problem. Let's see how this change was implemented.

Causes

When you change the Collation of a database, all columns with the following data types: char, nchar, varchar, nvarchar still keep the collation that was set when they were created. It's necessary to carefully analyze what's the best solution to minimize the impact on client applications and user queries.

The changes may occur in your queries results. Your report can obtain all the information and soon after collation change it may return anything, but this is the content of another article.

Change the collation only in one explicit label statement ( SELECT, INSERT, UPDATE or DELETE ) is also not recommended, because there is the possibility of slowing to statement during the conversion.

So in fact, the best solution in the context of this Forum post is to change the Collation of each column in the existing tables.

Just clarifying, new columns of string datatype as mentioned above, created after changing the Collation in the Database will adopt the same Collation setting indicated in the Database.

Diagnostic Steps

Once you diagnose the cause of the problem, we go to their proper resolution. There may be other solutions as an alternative, but the one indicated at the end of this article answers the question posted in the Forum in the simplest and most practical way possible.

 

Building the Scenario of the Problem

So that we can accurately simulate the problem and propose its solution, we build a table with little data, but similar to the situation shown in Threads Forum (Figure 1):

SELECT name, collation_name FROM sys.databases WHERE name = 'WI_Infra'
GO

CREATE TABLE BibliografiaTipos (
ID                        int   PRIMARY KEY   NOT NULL,
NM_USER           varchar(100)   COLLATE  SQL_Latin1_General_CP1_CI_AI  NOT NULL,
Descricao           varchar(250)   NOT NULL
)
GO 

sp_help BibliografiaTipos
GO

Figure 1 - Creating a table and indicating different Collations between the Database and Table Column

In order to understand why an error occurred with the proponent of the question while changing the Collation of the table's important to check what was the Collation in which the columns of the table belonged.

Checking the Scenario of the Problem

If there aren't constraints, just change the Collation of the column with the ALTER TABLE statement setting the column as "NOT NULL". See the code below:

ALTER TABLE BibliografiaTipos 
 ALTER COLUMN Descricao varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL
GO

This same solution displayed above solves most problems in Collation of the Database's with changed Collation tables. 

Still, we got the last problem: Primary Key and Unique index varchar columns.

Solution

The first part of the solution has already been accomplished by directing the proponent of the question to correct the ALTER TABLE statement to change the Collation of the column in all tables, as indicated in example. It's important to be clear that the indication of small fixes in the Causes solves much of the tables in the Database, because not have string columns as Primary Key mainly.

The second part of the solution we structure a simple and efficient way. The proponent of the question was advised to follow some steps:

  1. Generate scripts for create new tables to all tables where the string columns are Primary Key
  2. Include into new table names in this script the prefix "_NEW", e.g. "TB_YOURTABLE_NEW"
  3. Perform a FULL backup of the Database
  4. Execute the script to load data with INSERT / SELECT statement for each tables where the Primary Key is a varchar datatype column
  5. Execute the script to load data with INSERT / SELECT statement for each tables where the Foreign Key is a varchar datatype column and the Primary Key  is a IDENTITY number

Let's execute the script for load data from the old table to the new, where the Primary Key is a varchar datatype column.** **It's important to understand that's converted the Collation only of columns with string datatype in the current table.

INSERT INTO Class_SCHOOL**_NEW **(ID_CLASS, NM_CLASS)
SELECT ID_CLASS,
NM_CLASS  COLLATE SQL_Latin1_General_CP1_CI_AI
FROM Class_SCHOOL;

Let's execute the script for load data from the old table to the new, where the Foreign Key is a varchar datatype column and the Primary Key is a IDENTITY number you must declare all columns of the table. Including the "SET IDENTITY_INSERT" between the INSERT / SELECT statement to disable and enable the loading of data into Identity columns again. Perform this maintenance on a reserved schedule, where few or none users are connected. If necessary, change the status of your database to SINGLE_USER (Figure 2). After of the import verify the amount of records in both tables are the same (Figure 3).


**Figure 2 - **To prevent changes to others users in the Database, keep a single connection

SET IDENTITY_INSERT Frequences_STUDENT**_NEW ON
INSERT INTO Frequences_STUDENT_NEW**
(ID_STUDENT, NM_STUDENT)
SELECT
ID_STUDENT,
NM_STUDENT COLLATE SQL_Latin1_General_CP1_CI_AI  --Convert the Collation for new table
FROM Frequences_STUDENT;
SET IDENTITY_INSERT Frequences_STUDENT**_NEW **OFF
GO

--Verifying if the data was imported in the correct amount for the new table (See Figure 3)
SELECT 
            (SELECT COUNT(1) FROM Frequences_STUDENT) AS QTD_OLD,
            (SELECT COUNT(1) FROM Frequences_STUDENT_NEW) AS QTD_NEW
GO

--Rename the current table as "old table"
sp_rename 'Frequences_STUDENT', 'Frequences_STUDENT_OLD'
GO

--Rename the new table to replace the old table
sp_rename 'Frequences_STUDENT_NEW', 'Frequences_STUDENT'
GO

--After verifying that the data are updated in the new table, Excluding the "old table"
DROP TABLE Frequences_STUDENT_OLD
GO

Figure 3 - If not occur error converting Collation in the data, the amount of rows of both table must to be equal

Conclusion

To perform the database collation change it is necessary to perform a preliminary analysis to identify which tables are affected and how this change should be made. In some cases, it's necessary to execute the recreation of the table with another name, then import the table data with the previous Collation.

See Also

To strengthen your knowledge about COLLATE and IDENTITY I recommend to read these articles:

TechNet Library

Read some advanced links:

References

Read some advanced Web Site and Blog links:

Credits

This article was inspired by writing articles:

My special thanks to Richard Mueller for guiding me in TechNet Guru Awards in December, 2013, explaining how to organize my article into sections appropriately.

Other Languages

This article can also be found in the following languages​​: