Implicit Conversion can hit performance of your queries
If there is data type mismatch in the type of table field and the variable you use in where clause, the query will result in table or index scan instead of index seek even when there is a proper index on the field used in the where clause.
This is because SQL server will do implicit conversion by using the function on the field. Eg. if your table field is varchar and you define parameter as nvarchar, if you write something like below
declare @var1 nvarchar(20)=’test’
select * from table1 where field1=@var1
it will actually work like
select * from table1 where convert(nvarchar(20),field1) = @var1.
Thus any index on field1 will be ignored and a scan operation will run.
Be aware of this and use proper data types for your variables.
Also note the implicit conversion issue is not limited to field vs. variable..it will happen as well if you compare two fields with different data types..thus if same you have field in multiple tables, came the data type as same.
