When troubleshooting performance issues in SQL Server,one of the more common mistakes I come across are mismatched datatypes in predicates. This can cause a huge hit in performance and sometimes it won’t make a difference at all. Check out my sample code below, and watch out for IMPLICIT_CONVERSION !!!
/*Step 1 - Run this to stage temp table*/
use tempdb
go
if object_id(‘tempdb..[#FirstIndex]’,’U’) is not null
drop table [#FirstIndex]
CREATE TABLE #FirstIndex(id int,ID_VC VARCHAR(10),FirstName VARCHAR(100),LastName VARCHAR(100),City VARCHAR(100))
CREATE CLUSTERED INDEX CI_ID ON #FirstIndex (ID)
CREATE NONCLUSTERED INDEX IX_01 on #FirstIndex (city)
INSERT INTO #FirstIndex(ID,ID_VC,FirstName,LastName,City)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) rowID, ROW_NUMBER() OVER (ORDER BY a.name) rowID,
‘Bob’,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) %2 = 1 THEN ‘Smith’
ELSE ‘Brown’ END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) %10 = 1 THEN ‘New York’
WHEN ROW_NUMBER() OVER (ORDER BY a.name) %10 = 5 THEN ‘Baltimore’
WHEN ROW_NUMBER() OVER (ORDER BY a.name) %10 = 3 THEN ‘Los Angeles’
ELSE ‘Houston’ END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
/*Step 2 – Turn on execution plan and run */
/Step 2 – Set 1/
DECLARE @City VARCHAR(100) = ‘Baltimore’
,@City_N NVARCHAR(100) = N’Baltimore’
SELECT ID,City
FROM #FirstIndex
WHERE city = @City /Column datatype and variable datatype match/
SELECT Id,City
FROM #FirstIndex
WHERE City = @City_N /City column is VARCHAR and @City_N is NVARCHAR, this causes a IMPLICIT CONVERSION/
/Step 2 – Set 2/
DECLARE @ID INT = 519
,@ID_VC VARCHAR(10) = ‘519’
SELECT Id,City
FROM #FirstIndex
WHERE ID <= CAST(@ID as VARCHAR(10)) /ID is INT, CAST INT value as VARCHAR./
SELECT Id,City
FROM #FirstIndex
WHERE ID <= @ID_VC /ID is INT, variable is VARCHAR./
SELECT Id,City
FROM #FirstIndex
WHERE CAST(ID as VARCHAR(10)) <= @ID_VC /* ID is INT, but we’re going to CAST that column as VARCHAR to match the datatype of the variable @ID_VC. Changing the datatype of the column invalidates the indexes that exist, causing an INDEX or TABLE scan*/
drcdba.com 