difference between Int and Varchar (SQL Server) in Where clause?

Question

What is the difference between AID = 10 or AID = '10' when AID is an INT type in SQL Server?

Select *
from ATable
where AID = '10'

and

Select *
from ATable
where AID = 10

Answer 1

The difference is that SQL will need to do conversion in order to compare the varchar constant to the type of your column.

If you show the execution plan for the first query , you will see something like:

CONVERT_IMPLICIT(int, [@1], 0)

Edit

In the case above, conversion of a constant will only have a small performance impact. However, if the conversion is done instead on the column (and not the constant), this can result in scans which can have more serious consequences.

As per Remus answer, you can check the order of precedence of conversion here