Reduce Query Resource Use - SQL Server Query Performance Tuning - page 371

To understand how implicit data type conversion affects performance, consider the following example:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.Test1') )
DROP TABLE dbo.Test1;
CREATE TABLE dbo.Test1 (
Id INT IDENTITY(1,1),
MyKey VARCHAR(50),
MyValue VARCHAR(50));
CREATE UNIQUE CLUSTERED INDEX Test1PrimaryKey ON dbo.Test1 ([Id] ASC);
CREATE UNIQUE NONCLUSTERED INDEX TestIndex ON dbo.Test1 (MyKey);
GO
SELECT TOP 10000
IDENTITY( INT,1,1 ) AS n
INTO #Tally
FROM Master.dbo.syscolumns scl,
Master.dbo.syscolumns sc2;
INSERT INTO dbo.Test1
(MyKey,
MyValue)
SELECT TOP 10000
'UniqueKey' + CAST(n AS VARCHAR),
'Description'
FROM #Tally;
DROP TABLE #Tally;
SELECT t.MyValue
FROM dbo.Test1 AS t
WHERE t.MyKey = 'UniqueKey333';
SELECT t.MyValue
FROM dbo.Test1 AS t
WHERE t.MyKey = N'UniqueKey333';
After creating the table Test1 , creating a couple of indexes on it, and placing some data, two queries are defined.
Both queries return the same result set. As you can see, both queries are identical except for the data type of the
variable equated to the MyKey column. Since this column is VARCHAR , the first query doesn't require an implicit data
type conversion. The second query uses a different data type from that of the MyKey column, requiring an implicit data
type conversion and thereby adding overhead to the query performance. Figure 19-1 shows the execution plans for
both queries.