Some of my friends told me that using the ORDER BY clause decreases the performance of SQL statements. Do you believe it? Yes I am, as in such type of conditions the ORDER BY clause really decrease the performance of SQL query. But you have to understand the situations where you use or don't use the ORDER BY clause.
In my article, I am trying to illustrate those points.
In Microsoft SQL Server 2005/2008, 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.
Now I am trying to illustrate the causes of the problem
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.
Some other things that we must considers.
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.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment