Compare varchar and int - Implicit vs Expclicit conversions

Hi

While working on a query I stumbled across the following issue completely by mistake, but I believe it is worth mentioning.

I had a table with a varchar column that actually contained numbers, or so I believed. In my query as a result of lack of attention I wrote the following predicate
where col_name = 123456

Once I have executed the query, after a few seconds I got an error message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'asdf' to data type int.

I was a bit confused by this message since I had no place in my query where I performed such a conversion until I realized that a conversion is needed for my predicate.
I was pretty shocked that SQL Server chose to convert each varchar value to int and then perform the comparison, instead of just a one time conversion of the int literal into a varchar literal. You would expect the database optimizer to optimize the query... :-)

I fixed my query, but yet again, I performed another benchmark in order to check how much difference exists between the two possible behaviours - converting yourself, or let SQL Server convert for you. In my benchmark, I counted the records with a specific value, in a 1 million rows table. Here are the results:
No conversion - 186 milliseconds
Implicit Conversion - 330 milliseconds
Explicit Conversion - 186 milliseconds

That teaches us an important message - we must help the optimizer help us and we should always check what really happens when our queries are executing.

All of the above is true when there is no index on that varchar column. If there is an index on the varchar column, it's crucial to perform the explicit conversion, otherwise, SQL Server will not be able to perform an index seek and it will perform a table scan instead.

Here is the script:

create table varchar_table (var_col varchar(10))

set nocount on
declare @i int
set @i = 0
begin tran

while @i < class="blsp-spelling-error" id="SPELLING_ERROR_16">varchar_table values (cast(@i as varchar))

if ((@i % 10000)=0)
begin
commit
begin tran
end

set @i = @i+1
end
commit

declare @a datetime
declare @b datetime

set @a = getdate()
select count(*)
from varchar_table
where var_col = '50000'
set @b = getdate()
select 'No conversion', datediff(ms, @a, @b)

set @a = getdate()
select count(*)
from varchar_table
where var_col = 50000
set @b = getdate()
select 'Implicit conversion', datediff(ms, @a, @b)

declare @c int
set @c = 50000
set @a = getdate()
select count(*)
from varchar_table
where var_col = cast(@c as varchar)
set @b = getdate()
select 'Explicit conversion', datediff(ms, @a, @b)


Bye