When working with SQL Server databases, developers often use ADO.Net and lightweight ORMs to interact with the database. These tools allow the use of parameters without requiring the underlying SQL data types to be specified. While this can save developers time, it can also lead to performance issues if not used correctly.

One common issue arises when dealing with string parameters. In .Net, strings are Unicode by default. When using the System.Data.SqlClien.SqlParameter class, if a parameter is added without specifying the SQL data type, it will be inferred as nvarchar, the Unicode equivalent. However, most existing databases use char or varchar, not nchar or nvarchar.

When the database encounters a type mismatch, such as comparing nvarchar to varchar, it performs an implicit conversion to avoid losing information. This conversion has to be done for every row in the table, resulting in an expensive query with a non-optimal query plan.

Let’s consider an example to illustrate this issue. We have a table called Account with columns accountNumber of type CHAR(20) and accountName of type VARCHAR(100). If we query this table without specifying the SQL data type, the following query and plan are generated:

SELECT accountNumber, accountName
FROM Account
WHERE accountNumber = @accountNumber

Even though the query is looking for a specific account, an index scan occurs, which can be very expensive on a large table with millions of rows. This can lead to database contention and blocking, especially if the query is called multiple times in a tight loop.

To improve performance, it is recommended to specify the SQL data type when adding parameters. For example:

SELECT accountNumber, accountName
FROM Account
WHERE accountNumber = @accountNumber

By specifying the SQL data type as CHAR and setting the size to 20, the query and plan generated will be an index seek, which is far more optimal.

In conclusion, while it may save developers time to omit SQL data types when using parameters, it can lead to degraded performance and database contention. It is important to carefully consider the queries being generated by your code and study their performance. Running the SQL Profiler can help trap application queries and inspect them, allowing you to make informed decisions about specifying SQL data types.