SQL - .NET - SqlParameters - AddWithValue - Are there any negative performance implications when Param Type is not specified?

Question

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx

I'm used to adding sql parameters to a sqlCommand using the add() function. This allows me to specify the type of the sqlParameter, but it requires another line to set the value.

It's nice to use the AddWithValue function, but it skips the "specify the parameter type" step.

I'm guessing this causes the parameters to be sent over as strings contained within single quotes (''), but I'm not sure.

Is this the case, and does this cause significantly slower performance of the stored procedures?

Note: I understand that it is nice to validate user data on the .NET side of things by specifying the data type for params -- I'm only concerned about reflection-type overhead of AddWithValue either on the .NET or SQL side.

Answer 1

The type deduction happens client-side. Inside SqlParameter, it basically has a bit switch statement to determine what SQL type the value you specify corresponds to. So there's no difference in what is sent to SQL Server, but there is a very small overhead to determine what SQL type your .NET type corresponds to. In the grand scheme of things, the performance cost wouldn't even show up as a blip next to the time spent actually in the database call.

You can still do it in one line while specifying the SQL type:

cmd.Parameters.Add("@something", SqlDbType.NVarChar).Value = someString;