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 3 Cannot resolve collation conflict for 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_BIN
GO
USE testdb
GO
-- 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 code
CREATE PROCEDURE testsp_70
AS
BEGIN
CREATE TABLE #test( c char(10) NOT NULL PRIMARY KEY )
SELECT * FROM #test t1, test t2 WHERE t1.c = t2.c
END
GO
-- Sample stored procedure that shows how you make the temp
-- table use the current database's collation for a temporary table.
CREATE PROCEDURE testsp_2000
AS
BEGIN
CREATE TABLE #test( c char(10) COLLATE database_default
NOT NULL PRIMARY KEY )
SELECT * FROM #test t1, test t2 WHERE t1.c = t2.c
END
GO
EXEC testsp_70 EXEC testsp_2000 GO
-- Sample stored procedure showing the SQL Server 7.0 code
-- that uses SELECT INTO
CREATE PROCEDURE testsp2_70
AS
BEGIN
SELECT * INTO #test FROM test
ALTER TABLE #test ADD PRIMARY KEY ( c )
SELECT * FROM #test t1, test t2 WHERE t1.c = t2.c
END
GO
EXEC testsp2_70
GO
-- Sample stored procedure showing the SQL Server 2000 code
-- that uses COLLATE in the join criteria
CREATE PROCEDURE testsp2_2000
AS
BEGIN
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.c
END
GO
EXEC testsp2_2000
GO
-- DROP test database
USE master
GO
DROP 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:
- Set up a process to identify statements and queries that take longer than 1 minute to execute
- Automate the scheduling of this process in a way that avoids SQL Server restarts.