Passing SqlParameter from C # without a data type - performance review

Question

I tried two different ways to instantiate an 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 if there are performance implications when you are not using the data type and length when creating the parameters? How does SQL Server handle internally? Initially, I assumed that it implicitly converts the parameter data type to the table column data type, which is compared to Ex. which contains the sql query. But I'm not sure. Any help would be appreciated.

early

Answer 1

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

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

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

has a potentially big problem with which the ADO.NET runtime should guess which data type you want to use. This is a very good job - most of the time. But how should it guess the data type if you specify a NULL value? Or if you bet 50 is INT ? SmallInt ? TinyInt ? BigInt ?

Directly specifying the SqlParameter type can lead to unwanted (and undesirable) conversions, which can cost performance. But even worse: it can lead to direct errors, because if the guessing of the ADO.NET runtime is turned off, you may encounter errors at runtime (for example, when it cannot correctly assume that something is DATETIME or such) .