Tip

SQL Server collation conflict fixes

Michelle Gutzait, Contributor

 
As a DBA, you may need to work in multi-collation environments. You have different development teams, as well as packaged software from different vendors, and you have to satisfy everyone's needs regarding collation type.

Collations control

    Requires Free Membership to View

the physical storage of character strings in SQL Server. According to Books Online, "a collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared." Separate collations can be specified down to the column level in a table, and each column can be assigned different collations."

For more about collations, look up "collations, overview" under SQL Server Architecture in Books Online.

Unfortunately, if you are using temporary tables (or table parameters) in your application, and your database has a different collation than tempdb, you might end up with a message about collation conflict

TABLE OF CONTENTS
   Sample collation conflict error messages
   How to resolve collation problems
   Find problematic databases
   Development trick: Change temporary table collation after it's created
   Development trick: Change database and table collations in one script
   Development trick: Use views to access data with different collations

 
Sample collation conflict error messages

T-SQL Code Error Message
Use DbAnotherCollation
go
declare @t table (a varchar(20))
insert into @t
select a from aTable
select * from @t t,aTable a
where t.a = a.a
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
Use DbAnotherCollation
go
create table #t (a varchar(20))
insert into #t
select a from aTable
select * from #t, aTable
where #t.a = aTable.a
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
Use DbAnotherCollation
go
select * into #t from aTable
select * from #t, aTable
where #t.a = aTable.a
No error message: the select… into command copies the collation property directly from the table!

 
How to resolve collation problems

You can solve this problem in two ways:

1. Every time you use a temporary table (or table parameter), create the temp table with the desired collation or add the COLLATE phrase when necessary.
2. Have a different SQL Server instance for each collation.

Neither option is ideal, but I prefer the second for two main reasons:

1. If database collations change in the future, no changes in the application will be required.
2. Indexes on columns with the COLLATE phrase in the WHERE clause can't be used.

 
Find problematic databases

Here is a quick way to find databases with a different collation then tempdb in your instance.

 Set nocount on create table #tmp (DatabaseName sysname, CollationName sysname) insert into #tmp (DatabaseName, CollationName) EXEC sp_MSForEachDB 'select ''?'',convert(varchar(100),DATABASEPROPERTYEX(''?'' , ''COLLATION'')) where DATABASEPROPERTYEX(''?'' , ''COLLATION'') != DATABASEPROPERTYEX(''tempdb'' , ''COLLATION'')' select * from #tmp

If you are not sure about the collation name, the following query might help.

select * from ::fn_helpcollations()

If you are aware of possible collation problems, you can develop your application in a preventive manner. Here are tips and tricks on how to change the collation if you need to.

Some of the following ideas are based on this handy Change Collation article.
 
Development trick: Change temporary table collation after it's created

After creating the temporary table, you can alter it to use the current database's collation. This applies only to temporary tables since table variables can't be altered after you create them. Note that the temporary table cannot be created inside another stored procedure because the calling program will not recognize it.

The following stored procedure gets the temporary table's name as input and changes the character fields to the current database collation.

Click here for the sp_ChangeTempTableCollation stored procedure.

The only issue is that if you have text and/or ntext columns in the temporary table, you should explicitly mention the fields' names in subsequent commands in which the table is used. These fields cannot be altered, but they can be dropped and recreated. For instance, select * from # will not be the same after the table is altered to the new collation. Instead, you should execute: select , , …  from #.

Here is an example of how to run this stored procedure. The collation of my database is French_CI_AI. Tempdb has the SQL_Latin1_General_CP1_CI_AS collation.

 -- Create the following temp table: create table #tmp (col0 int, col1 varchar(10) NOT NULL, col2 text, col3 nvarchar(20) collate Chinese_PRC_CI_AI, col4 ntext collate French_CI_AI) go -- Show the fields and characteristics before running the SP: select COLUMN_NAME,DATA_TYPE,CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' WHEN 'No' THEN 'NOT NULL' END as 'NULLABLE', CHARACTER_MAXIMUM_LENGTH AS LENGTH, COLLATION_NAME FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS WHERE object_id('tempdb..'+TABLE_NAME) = object_id('tempdb..#tmp') Go -- Execute the Stored Procedure exec sp_ChangeTempTableCollation '#tmp' go -- Show the fields and characteristics after running the SP: select COLUMN_NAME,DATA_TYPE,CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' WHEN 'No' THEN 'NOT NULL' END as 'NULLABLE', CHARACTER_MAXIMUM_LENGTH AS LENGTH, COLLATION_NAME FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS WHERE object_id('tempdb..'+TABLE_NAME) = object_id('tempdb..#tmp') Go

Results before running the stored procedure:

COLUMN_NAME DATA_TYPE NULLABLE LENGTH COLLATION_NAME
col0 Int NULL NULL NULL
col1 Varchar NOT NULL 10 SQL_Latin1_General_CP1_CI_AS
col2 Text NULL 2147483647 SQL_Latin1_General_CP1_CI_AS
col3 nvarchar NULL 20 Chinese_PRC_CI_AI
col4 Ntext NULL 1073741823 French_CI_AI

Results after running the stored procedure:

COLUMN_NAME DATA_TYPE NULLABLE LENGTH COLLATION_NAME
col0 Int NULL NULL NULL
col1 Varchar NOT NULL 10 French_CI_AI
col3 nvarchar NULL 20 French_CI_AI
col4 Ntext NULL 1073741823 French_CI_AI
col2 Text NULL 2147483647 French_CI_AI

Note that since there are text,ntext columns, the order is changed.
 
Development trick: Change database and table collations in one script

If the applications tolerate this, you can change the database collation to be the same as that of tempdb. I recommend doing that as follows:

1. Generate script for all the objects in the database.
2. If the COLLATE phrase was generated in the script, delete it.
3. Create a new database with the new collation.
4. Create all objects except for foreign keys (for not having a conflict).
5. Copy data with Data Transformation Services (DTS).
6. Add foreign key constraints.
7. Back up the new database.
8. Back up the old database and delete it if needed.

If you don't have enough space for both databases, you can try running the script below. Please backup your database before doing so!

Click here for the collation conflict script.

The script might run a long time, depending on your database size. The column order in a table may vary, too, since some of the columns are dropped and recreated.

Another issue that may arise (and unfortunately happened to me during testing) is that if sort order changes in the new collation, you may get errors that data is not unique as expected (i.e., trying to recreate unique indexes). If you run this script multiple times in a testing environment, please don't forget to clean your transaction log once in a while.

Before you run the script, make sure nobody else is using the database -- or else it would fail on timeout trying to change the database option to "Single User."

In this tip, I used the database script from the article mentioned above and modified it to suppress the needs of the Visual Basic application. This is a complex script and you may find bugs, as I did, in the original script. Please don't hesitate to e-mail your remarks. Furthermore, this script might change in the future for improvement and bug-fixing purposes. I am aware of the fact that some parts here require improvement.

Again, you may click here for the collation conflict script.
 
Development trick: Use views to access data with different collations

If you have a large database, it may take a long time to change the database collation. You can create views that query the tables with a different collation. The view would look exactly like the table, but the string columns will be fetched with the COLLATE phrase.

Depending on the queries and the amount of comparison you have on string columns, this solution might not suit you from a performance perspective. Unfortunately, this way the COLLATE phrase prevents SQL Server from using indexes on these fields. Let's see how it works.

-- Create two tables with different collations:
create table Table1 (Col1 int, Col2 varchar (100) collate French_CI_AS)
create table Table2 (Col3 int, Col4 varchar (100) collate French_BIN)
go
-- Create View on the second table with COLLATE phrase:
create view View1 as select Col3, Col4 collate French_CI_AS as Col4 from Table2
go

-- Populate Table1 with 10000 different strings:
declare @i int
set @i = 1
while @i <= 10000
begin
insert into Table1 select @i, replicate(CHAR(@i),100)
set @i = @i + 1
end

-- Populate Table2 with same rows as in Table2:
insert into Table2 select Col1, Col2 from Table1
go

-- Create indexes on both tables:
create clustered index Table1_idx on Table1 (Col2)
create clustered index Table2_idx on Table2 (Col4)
go

After running the queries, I received the following results:

select * from Table1 where col2 like 'a%'

Col1 Col2
65 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
97 Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

select * from Table2 where col4 like 'a%'

Col3 Col4
97 Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

select * from View1 where col4 like 'a%'

Col1 Col2
65 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
97 Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Here are the execution plans:

Here are the detailed actions for each query:

The index was not used. Let's see what happens if we add index on the view:

-- Create view with bound schema (so it can be indexed):
create view View2 with SCHEMABINDING
as select Col3, Col4 collate French_CI_AS as Col4 from dbo.Table2
go
create clustered index View2_idx on dbo.View2 (Col4)
go

We get an error message:
Server: Msg 1933, Level 16, State 1, Line 1
Cannot create index because the key column 'Col4' is non-deterministic or imprecise.

Maybe we can trick it by creating another view on this view.

create view View3 with SCHEMABINDING
as select Col3, Col4 from dbo.View2
go
create clustered index View3_idx on dbo.View3 (Col4)
go

Again, we get the error message:
Server: Msg 1933, Level 16, State, Line 1
Cannot create index because the key column 'Col4' is non-deterministic or imprecise.

Finally, querying the data through views that use the COLLATE phrase impedes SQL Server from using indexes on the string columns.

Conclusion

The best way to deal with collation conflicts is not to have them in the first place, but sometimes you have no choice. The best solution seems to be to have a different SQL Server instance for each collation.

About the author: Michelle Gutzait works as a senior database consultant for Itergy International Inc., an information technology consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. She has worked exclusively with SQL Server for the last 10 years. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.

This was first published in May 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.