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

Table 9-13: Comparison of Explicit Conversion Costs

Method SubTree Cost CPU Reads Duration

Conversion 0.00328314 0 2 37ms

No Conversion 0.00328314 0 2 10ms

The same type of behavior occurs if you reverse the situation and search a Unicode column with a non-
Unicode parameter, except that SQL Server handles this a little differently. To experiment, run this query
against the Sales.SalesOrderHeader table that has an AccountNumber column that is of type NVARCHAR .

DECLARE @ACCOUNTNUMBER_UNICODE NVARCHAR(30)
SET @ACCOUNTNUMBER_UNICODE = N'10-4020-000002'

SELECT AccountNumber FROM Sales.SalesOrderHeader
WHERE AccountNumber = @ACCOUNTNUMBER_UNICODE
go
DECLARE @ACCOUNTNUMBER VARCHAR(30)
SET @ACCOUNTNUMBER = '10-4020-000002'

SELECT AccountNumber FROM Sales.SalesOrderHeader
WHERE AccountNumber = @ACCOUNTNUMBER

When you examine these query plans you'll notice that they both look the same. If you look at the plan
details, you'll see there is an implementation difference, but not as dramatic as the first example.

Index Seek(OBJECT:([IX_SalesOrderHeader_AccountNumber]), SEEK:([AccountNumber]=
CONVERT_IMPLICIT(nvarchar(30),[@ACCOUNTNUMBER],0)) ORDERED FORWARD)

After you study this for a minute, you may wonder why the optimizer didn't choose a plan like this
when converting a NVARCHAR parameter to search a VARCHAR column. The answer is because of data
type precedence. When comparing two compatible data types, the once with lower precedence will be
converted. When comparing a VARCHAR and NVARCHAR data type, VARCHAR has the lower precedence. In
the first example, the contents of the rows (of type VARCHAR ) have to be converted to compare to the
NVARCHAR parameter. In the second example, only the parameter (of type VARCHAR ) needs to be converted
for comparison to a NVARCHAR column.

This issue is not limited to just NVARCHAR and VARCHAR data types. You can see this type of behavior
on any other compatible data types under similar search argument or join conditions. Just look for the
CONVERT_IMPLICIT operation occurring in your query plans to see if you have this sort of issue.
Performance can be easily improved by rewriting the SQL statement to match parameters to the column
data types.

Using Searchable Arguments
Similar to the Implicit Conversion issues and probably the next easiest issues to fix for performance
tuning are incorrectly coded WHERE predicates and search arguments. The optimizer can't save you