Will implicit data type conversion in SQL affect performance?

Question

I have 2 samples that effectively do the same thing -- Sample 1:

command.Parameters.Add(ParamFoo, SqlDbType.Int)
command.Parameters(ParamFoo).Value = fooVal

Sample 2:

command.Parameters.AddWithValue(ParamBar, barVal)

Which sample would be recommended? I know that the second sample is making some conversions. Wouldn't it be best to not require conversions and go with the first sample?

Answer 1

As you posted your code, is completely irrelevant. Both variants will perform identically, there will be no way you can measure the cost of in-client converts against the huge background of the database call.

However, there is a very very big known problem when barVal is of type String. The SqlClient will add the parameters as type NVARCHAR and when used in SQL expressions in comparing with a VARCHAR column, the SQL type precedence rules will require the column to be converted to NVARCHAR instead of the variable to be converted to VARCHAR, and this changes index seeks into table scans and has disastrous performance consequences.