I wanted to put up a brief post showing the impact of an implicit conversion on the performance of a query (or not......). In the example i will show an implicit conversion negatively impacting query performance and an implicit conversion that does not impact performance.....
So, we need to setup the test environment using the code below.
SET ANSI_WARNINGS OFF
--******** Create Test Data ******************
CREATE TABLE #data_test(ukey INT IDENTITY(1,1) PRIMARY KEY , first VARCHAR(200),second VARCHAR(200))
DECLARE @first INT, @second INT
SELECT @first = 1
WHILE @first < 250000
BEGIN
INSERT #data_test
SELECT REPLICATE(@first,@first), REPLICATE(@first,@first)
SELECT @first = @first +1
END
CREATE NONCLUSTERED INDEX stuf_1 ON #data_test (first)
--******** End of Test Data ******************
Now, with the test data in place we can run the following 2 queries and observe the differences.
/* This uses a variable declared as an NVARCHAR */
EXEC sp_executesql N'SELECT * FROM #data_test WHERE first = (@p0) ', N'@p0 nvarchar(200)',@p0 = N'1'
--Scan count 1, logical reads 3093, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
/* This uses a variable declared as an VARCHAR */
EXEC sp_executesql N'SELECT * FROM #data_test WHERE first = (@p0)', N'@p0 varchar(200)',@p0 = '1'
--Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Below each statement is the io incurred and the difference on this tiny little table is > 3000 IO's just because we used unicode (nvarchar) instead of non-unicode (varchar) and i`m sure you can imagine that on a larger table this becomes a significant overhead. So, why has this happened? Lets take a look at the plans.
We have 2 very different plans just because we have used a datatype in our query that varies from our underlying datatype. What we see happening here is that SQL Server has introduced a CONVERT_IMPLICIT into the queryplan which leads to the additional io.
I also mentioned that the implicit conversion may not always lead to a performance penalty so lets explore that point. To illustrate this we need to change the underlying datatypes in our test table from varchar to nvarchar. I dropped the test table and ran the new create table below and repopulated it with data using the code at the beginning of the post.
CREATE TABLE #data_test(ukey INT IDENTITY(1,1) PRIMARY KEY , first NVARCHAR(200),second NVARCHAR(200))
Next we run the same queries that we did earlier.
/* This uses a variable declared as an NVARCHAR */
EXEC sp_executesql N'SELECT * FROM #data_test WHERE first = (@p0) ', N'@p0 nvarchar(200)',@p0 = N'1'
--Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
/* This uses a variable declared as an VARCHAR */
EXEC sp_executesql N'SELECT * FROM #data_test WHERE first = (@p0)', N'@p0 varchar(200)',@p0 = '1'
--Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The performance is identical! But surely we should still be incurring a conversion overhead because this time the conversion should be from non-unicode data to unicode data. Maybe there is still a conversion so lets take a look at the plans.
As you can see, graphically the queries appear to be identical but when we bring up the tooltip for the index seek on the second query which is the one with the non-unicode data we can see the conversion is occuring! The difference here is that the convert_implicit has moved to the seek predicates so it is occuring during the index seek where as previously it was showing as a predicate. I think this is because when you convert from non-unicode to unicode there is no potential for data loss but when you go from unicode to non-unicode there is but i may be wrong and if anyone knows i would be interested to hear from you.
It is possible that there is some additional CPU overhead being incurred by the conversion but i did not detect any notable differences in my testing but it may show on very large data sets.
So to conclude, it is important to know your underlying datatypes and map to them appropriately. It is also worth noting that sometimes code which is automatically generated by a tool may also fall foul of the implicit conversion trap. If a conversion is required i would recommend making it explicitly rather than implicitly. Some conversions may not be obvious as pointed out by Jason Massie who also highlighted a very useful query from Umachandar Jayachandran that will identify cached plans with the convert_implicit operator so that you can examine them in more detail.