This is not just about implicit conversion. This is about implicit conversion that happens without our notice, that might hinder the performance. See the code below:
USE AdventureWorks2008
GO
DECLARE @FirstName varchar(50)
SET @FirstName = 'Gigi'
SELECT * FROM Person.Person
WHERE FirstName = @FirstName
No major issue. It returns data as we want. Enable Actual Execution Plan and run the code again. Do you see what I see?
SQL Server converts the value which is varchar into nvarchar which can be a costly operation in a certain situation. SQL Server does this because char and varchar have lower precedence than nchar and nvarchar.
This implicit can be simply overcome by making the variable as nvarchar. You can see the same when integer value is passed to a string type column that contains integer values.
Although this is a simple and not a major issue as we see, it can slow down your queries.
2 comments:
Hi Dinesh,
Thank you for creating the awareness.
The worst performance can happen when the database is having a varchar column while the (local) variable is nvarchar. in this case, there is a possibility of implicit conversion of the column. That means the conversion will happen against all rows and not against the variable. hat is much much costlier.
Hi Preethi,
It is a great point. Thanks for sharing it.
Post a Comment