Passing SqlParameter from C# without datatype - performance consideration

Question

I tried 2 different ways to instantiate my SqlParameter object

SQLParameter p = new SqlParameter("@DatabaseId", SqlDbType.VarChar, 5, Quarter.DataBaseId.ToString(); //line 1

SQLParameter p = new SqlParameter("@DatabaseId", Quarter.DataBaseId); // line 2

I was wondering whether there are any performance implications when you don't use datatype and length while creation of parameters? How does SQL Server handle internally? Initially I assumed it would implicitly convert parameters datatype to the datatype of the table column it is compared against Ex. in where clause of sql query. But I am not sure. Any help will be appreciated.

thanks in advance

Answer 1

I don't think performance is the major problem here - although, with string (varchar) parameters, if you don't specify the length, the ADO.NET runtime will set the max length of the parameter to the actual length, which may or may not be such a great idea....

However: not explicitly specifying the data type with this statement:

SqlParameter p = new SqlParameter("@DatabaseId", Quarter.DataBaseId);

has the potentially big issue that the ADO.NET runtime has to guess what datatype you want to use. It does a pretty good job - most of the time. But how should it guess the datatype if you supply a NULL value? Or if you supply 50 - is that an INT? SmallInt? TinyInt? BigInt?

Not explicitly specifying the SqlParameter type can lead to unwanted (and undesirable) conversions - which might cost performance. But worse: it can lead to outright errors, since if the ADO.NET runtime's guess is off, you might end up with runtime errors (e.g. when it cannot properly guess that something is a DATETIME or such).