.
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.
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.
[IMAGE][IMAGE] Development trick: Change database and table collations in one script[IMAGE] Return to Table of Contents
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.
[IMAGE][IMAGE] Development trick: Use views to access data with different collations[IMAGE] Return to Table of Contents
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:
[IMAGE]
Here are the detailed actions for each query:
[IMAGE]
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.
