Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 281

SELECT CreditCardApprovalCode FROM Sales.SalesOrderHeader
WHERE CreditCardApprovalCode = @CreditCardApprovalCode_UNICODE
GO
DECLARE @CreditCardApprovalCode VARCHAR(30)
SET @CreditCardApprovalCode = '539435Vi62867'
SELECT CreditCardApprovalCode FROM Sales.SalesOrderHeader
WHERE CreditCardApprovalCode = @CreditCardApprovalCode
Running this query generates the following query plans, seen in Figure 9-10.
Figure 9-10
Note that when the query has a Unicode parameter there are two extra operations in the query plan.
The Constant Scan and Compute Scalar Operators calculate the upper and lower range of the statistics
histogram where the converted value of the Unicode parameter falls. You can see this in the plan details:
Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
|--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=
GetRangeThroughConvert([@CreditCardApprovalCode_UNICODE],
[@CreditCardApprovalCode_UNICODE],(62))))
| |--Constant Scan
|--Index Seek(OBJECT:([IX_SalesOrderHeader_CreditCardApprovalCode]), SEEK:( [Credit-
CardApprovalCode] > [Expr1005] AND [CreditCardApprovalCode] < [Expr1006]),
WHERE:(CONVERT_IMPLICIT(nvarchar(15), [CreditCardApprovalCode],0)
=[@CreditCardApprovalCode_UNICODE]) ORDERED FORWARD)
The GetRangeThroughConvert definition uses the statistics histogram to calculate the upper and lower
range values. The Seek then can occur against the index using the converted lower and upper range
values and then the implicit conversion is applied to each value within the range. This still takes three
times as long as using a parameter that doesn't require conversion. However, as you can see in Table 9-13,
this is much more efficient than scanning the entire index.