IMPLICIT CONVERSION

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*/

Posted in SQL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s