Article ID: 951935 - View products that this article applies to.
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.
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.
To work around this problem, use one of the following methods.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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:
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.
Steps to reproduce this problemRun the following statement in SQL Server Management Studio.
The following is the output:
For more information about the available SQL tertiary collation, see the "Remarks" section of the following Microsoft Developer Network (MSDN) Web site:
For more information about the DROP INDEX statement, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms176118.aspxFor more information about the CREATE INDEX statement, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms188783.aspxFor more information about the ALTER TABLE statement, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms190273.aspxFor more information about the TERTIARY_WEIGHTS function, visit the following MSDN Web site:
Article ID: 951935 - Last Review: June 25, 2008 - Revision: 2.1