Introduction

ADO.Net and light weight ORMs allow the use of parameters without requiring the underlying SQL data types to be specified.  The SQL type is inferred from the parameter value and saves developers time from matching data types to the database schema.  Most of the time this inference is correct. When it isn't, it could lead to bad performing queries without much warning.

Strings in .Net are Unicode. This carries over when using System.Data.SqlClien.SqlParameter.  If you add a SqlParameter without specifying the SQL datatype, the type will be inferred as nvarchar  the equivalent for Unicode values.  This is generally what you want for new development, but most existing databases use char or varchar, not the Unicode nchar or nvarchar. When the database is faced with a type mismatch of nvarchar being compared to varchar, it performs an implicit conversion so as not to lose information. This widening conversion has to be done for every row in the table resulting in an expensive query with a non-optimal query plan.

Let's take the following table as an example.

CREATE TABLE Account
  (
    accountNumber CHAR(20) NOT NULL
   ,accountName VARCHAR(100)
   ,CONSTRAINT PK_Account PRIMARY KEY ( accountNumber )
  );
GO
INSERT Account ( accountNumber ) SELECT '123456';

No SQL Data Type Specified 

Now let’s query this table without specifying the SQL datatype.

using (var connection = new SqlConnection(connectionString)) {
    var sql = @"select accountNumber, accountName from Account where accountNumber = @accountNumber"; 
    connection.Open();
    var sqlCmd = new SqlCommand(sql, connection);
    sqlCmd.Parameters.Add(new SqlParameter {ParameterName = "@accountNumber", Value = "R123456" });
    using (var reader = sqlCmd.ExecuteReader()) {
        while (reader.Read()) {
            Console.WriteLine(reader["accountNumber"]);
        } 
    }
}

When we query this table without specifying the SQL datatype, the following query and plan are generated. Note that a scan occurs, despite the query only looking for a specific account.

On a large table with millions of rows, this index scan can become very expensive.  This is especially true when this query is called multiple times in a tight loop leading to more database contention and further blocking.

With SQL Data Type Specified

Now let’s query this table with a specified SQL datatype.

using (var connection = new SqlConnection(connectionString)) {
    var sql = @"select accountNumber, accountName from Account where accountNumber = @accountNumber"; 
    connection.Open();
    var sqlCmd = new SqlCommand(sql, connection);
    sqlCmd.Parameters.Add(new SqlParameter {ParameterName = "@accountNumber",
         SqlDbType = SqlDbType.Char,
         Size = 20,
         Value = "R123456" });
    using (var reader = sqlCmd.ExecuteReader()) {
        while (reader.Read()) {
            Console.WriteLine(reader["accountNumber"]);
        } 
    }
}

When we query this table with a specified SQL datatype, the following query and plan are generated.

On a large table with millions of rows, the Index Seek will be far more optimal then the previous Index Scan.

Conclusion

Knowing what queries are being generated by your code can potentially save you significant performance penalties.  Run the SQL Profiler to trap application queries and inspect them.  Although it can save developers time to omit sql data types, doing so can lead to degraded performance and database contention. Carefully make this choice by inspecting queries being generated and studying their performance.