Collation Conflict

Congratulations to Charles Rummel, DBA/Programmer at Chicago-based Morningstar, and Yuval Peleg, DBA at Eyron in Israel. Charles won first prize of $100 for the best solution to the November Reader Challenge, "Collation Conflict." Yuval won second prize of $50. Here’s a recap of the problem and the solution to the November Reader Challenge.

Problem

Thomas administers databases that are hosted on several SQL Server 7.0 servers with different collation settings. As part of an upgrade, he’s trying to consolidate these databases into one powerful SQL Server 2000 server. He can easily make this move because SQL Server 2000 supports collation at different levels (i.e., server, database, column). The new SQL Server 2000 server is installed with the default settings for each SQL Server 7.0 collation. But during his upgrade testing, Thomas notices that some of the existing stored procedures fail with the error message:

Server: Msg 446, Level 16, State 9, Line 3Cannot resolve collation conflictfor equal to operation.

Help Thomas determine the cause of this problem and the possible solution.

Solution

The error message says that a collation conflict occurs while SQL Server is trying to compare two columns or expressions that have different collations. During his investigation, Thomas uncovers a significant clue: The error occurs in stored procedures that use temporary tables. SQL Server 2000 has precedence for collation and a predefined set of rules that determine an expression’s collation. Thomas remembers that temporary tables created within a stored procedure use the tempdb database’s collation instead of the current user database’s collation. In this case, because the server is installed with the default case-insensitive collation setting, tempdb, which is created at installation, is assigned the default collation. However, the databases that are migrating from SQL Server 7.0 use nondefault collation settings, so conflicts result.

To instruct SQL Server to use the current user database’s collation for the temporary table’s character columns, Thomas can use the COLLATE clause as part of the temporary table’s character column definitions and name the collation database_default. This special collation name assigns the collation for the temporary table columns as the current user database or the database in which the stored procedure resides instead of the tempdb database.

As a second solution, Thomas can revise the stored procedure so that it uses a SELECT INTO clause in SQL Server 7.0 code to create the temporary table instead of CREATE TABLE. When the stored procedure uses SELECT INTO, the columns in the temporary table automatically inherit the collation of the columns or expressions in the SELECT list. However, this method requires the user to create constraints on the temporary table after the stored procedure uses the SELECT INTO. Finally, Thomas can also resolve the COLLATION conflicts by explicitly using the COLLATE clause as part of the join criteria in SQL Server 2000 code to specify the database collation.

The following sample CREATE TABLE statement shows how to use the COLLATE clause. The script demonstrates the problem by creating a database with a nondefault collation setting and creating two stored procedures—one that includes the SQL Server 7.0 code and another that contains the COLLATE clause’s modifications. The sample also shows how to use the SELECT INTO clause and the COLLATE clause in the join criteria.

/* Create a database with a nondefault collation. Ensure that the server default is different from the specified default. */

CREATE DATABASE testdb COLLATE         SQL_Latin1_General_CP437_BINGOUSE testdbGO-- Create a dummy table.CREATE TABLE test( c char(10) NOT NULL PRIMARY KEY )GO-- Sample stored procedure that shows the SQL Server 7.0 codeCREATE PROCEDURE testsp_70ASBEGIN   CREATE TABLE #test( c char(10) NOT NULL PRIMARY KEY )   SELECT * FROM #test t1, test t2 WHERE t1.c = t2.cENDGO

-- Sample stored procedure that shows how you make the temp-- table use the current database's collation for a temporary table.CREATE PROCEDURE testsp_2000ASBEGIN   CREATE TABLE #test( c char(10) COLLATE database_default          NOT NULL PRIMARY KEY )      SELECT * FROM #test t1, test t2 WHERE t1.c = t2.cENDGO

EXEC testsp_70EXEC testsp_2000GO

-- Sample stored procedure showing the SQL Server 7.0 code-- that uses SELECT INTOCREATE PROCEDURE testsp2_70ASBEGIN   SELECT * INTO #test FROM test      ALTER TABLE #test ADD PRIMARY KEY ( c )      SELECT * FROM #test t1, test t2 WHERE t1.c = t2.cENDGO

EXEC testsp2_70GO-- Sample stored procedure showing the SQL Server 2000 code-- that uses COLLATE in the join criteriaCREATE PROCEDURE testsp2_2000ASBEGIN   CREATE TABLE #test( c char(10) NOT NULL PRIMARY KEY )      SELECT * FROM #test t1, test t2 WHERE t1.c COLLATE             SQL_Latin1_General_CP437_BIN = t2.cENDGOEXEC testsp2_2000GO-- DROP test databaseUSE masterGODROP DATABASE testdb

Now, test your SQL Server savvy in the December Reader Challenge, "Stalking the Statements" (below). Submit your solution in an email message to [email protected] by November 20. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here’s the challenge: Ryan is the DBA for several SQL Server 2000 installations. Lately he’s noticed that queries and statements coming in from his company’s Web servers to in-house SQL Server instances are executing more slowly. He wants to devise an automated method of flagging the worst-performing statements and queries without user intervention so that he can tune the queries. Help Ryan take the following actions: