• Login
• Become a member
• RSS
• Part of the TechTarget network

 
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

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:

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

 
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.

Join the conversationComment

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.

All Rights Reserved,Copyright 2005 - 2013, TechTarget