Article ID: 951935 - Last Review: June 25, 2008 - Revision: 2.1

Query performance is slow when you run a query that contains an ORDER BY clause on a table column of an SQL tertiary collation

View products that this article applies to.

On This Page

Expand all | Collapse all

SYMPTOMS

In Microsoft SQL Server 2005, you create a table. The table contains a column of a non-Unicode data type. Additionally, the collation of the column is an SQL tertiary collation. Then, you create an index that is not unique on the column. You run a query against the table by specifying the ORDER BY clause on the column. In this scenario, the performance of the query is slow. Additionally, if the query is executed from a cursor, the cursor is converted implicitly to a keyset cursor.
Back to the top

CAUSE

This problem occurs because the query optimizer does not use the index. In the execution plan of the query, the SORT operator appears first. Then, the Compute Scalar operator that contains the TERTIARY_WEIGHTS function appears.
Back to the top

WORKAROUND

To work around this problem, use one of the following methods.
Back to the top

Method 1

  1. Drop the index on the table.
  2. Change the data type of the column to a Unicode data type.
  3. Re-create the index that is based on the column.
For example, you can run the following statement to work around this problem for the scenario in the "Steps to reproduce this problem" section.
DROP INDEX Table1.indx
GO

ALTER TABLE Table1
ALTER COLUMN col1 NCHAR (10)
GO

CREATE INDEX indx ON Table1 (col1)
GO
Back to the top

Method 2

  1. Drop the index on the table.
  2. Add a computed column to the table by using the TERTIARY_WEIGHTS function on the column that has the SQL tertiary collation.
  3. Re-create the index that is based on the column and on the computed column.
  4. Add the computed column to the ORDER BY clause.
For example, you can run the following statement to work around this problem for the scenario in the "Steps to reproduce this problem" section.
DROP INDEX Table1.indx
GO

ALTER TABLE Table1
ADD COL3 AS TERTIARY_WEIGHTS (col1)
GO

CREATE INDEX indx ON Table1 (col1, col3)
GO

SELECT col1 FROM Table1
ORDER BY col1, col3
GO
Back to the top

Method 3

  1. Drop the index on the table.
  2. Re-create a unique index that is based on the column.
For example, you can run the following statement to work around this problem for the scenario in the "Steps to reproduce this problem" section.
DROP INDEX Table1.indx
GO

CREATE UNIQUE INDEX indx ON Table1 (col1)
GO
Back to the top

Method 4

  1. Drop the index on the table.
  2. Change the collation of the column to a collation that is not an SQL tertiary collation.
  3. Re-create the index that is based on the column.
For example, you can run the following statement to work around this problem for the scenario in the "Steps to reproduce this problem" section.
DROP INDEX Table1.indx
GO

ALTER TABLE Table1
ALTER COLUMN col1 CHAR (10) COLLATE SQ_Latin1_General_Cp850_BIN
GO

CREATE INDEX indx ON Table1 (col1)
GO
Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Back to the top

MORE INFORMATION

In a tertiary collation definition, certain characters are considered equal for comparison. However, the weights of these characters are considered different when you order these characters. For example, a string comparison is not case-sensitive. However, when you use the ORDER BY clause to order these strings, uppercase characters appear before lowercase characters.

The following data types are non-Unicode data types:
  • char
  • text
  • varchar
The following data types are Unicode data types:
  • nchar
  • ntext
  • nvarchar
To perform the SORT operation on non-Unicode string expressions that are defined in an SQL tertiary collation, SQL Server 2005 requires a binary string of weights for each character in the string expression.

A keyset cursor creates a table in the tempdb database. The table stores the set of keys that uniquely identify the rows in the cursor.
Back to the top

Steps to reproduce this problem

Run the following statement in SQL Server Management Studio.
CREATE TABLE Table1
(col1 CHAR(10) COLLATE SQL_Danish_Pref_CP1_CI_AS, col2 INT)
GO

CREATE INDEX indx ON Table1 (col1)
GO

SET SHOWPLAN_TEXT ON
GO

SELECT col1 FROM Table1
ORDER BY col1
GO

SET SHOWPLAN_TEXT OFF
GO
The following is the output:
|--Sort(ORDER BY:([Test].[dbo].[Table1].[col1] ASC, [Expr1004] ASC))
     |--Compute Scalar(DEFINE:([Expr1004]=tertiary_weights([Test].[dbo].[Table1].[col1])))
          |--Index Scan(OBJECT:([Test].[dbo].[Table1].[indx]))
For more information about the available SQL tertiary collation, see the "Remarks" section of the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/ms186881.aspx (http://msdn.microsoft.com/en-us/library/ms186881.aspx)
Back to the top

REFERENCES

For more information about the DROP INDEX statement, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms176118.aspx (http://msdn.microsoft.com/en-us/library/ms176118.aspx)
For more information about the CREATE INDEX statement, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms188783.aspx (http://msdn.microsoft.com/en-us/library/ms188783.aspx)
For more information about the ALTER TABLE statement, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms190273.aspx (http://msdn.microsoft.com/en-us/library/ms190273.aspx)
For more information about the TERTIARY_WEIGHTS function, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms186881.aspx (http://msdn.microsoft.com/en-us/library/ms186881.aspx)
Back to the top
APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
Back to the top
Keywords: 
kbexpertiseadvanced kbtshoot kbprb KB951935
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations

 

Related Support Centers