Parameter Types From .NET: Stopping Implicit Conversions at the Source

The query looks right, but the application sends the wrong data type. Explicit parameter types keep that mismatch out of the execution plan. Start with the value the server receives.

Read the Contract on Both Sides

I check application parameters before recommending an index for a simple lookup. An indexed column doesn't guarantee an efficient search. The comparison also needs compatible types.

A varchar column stores non-Unicode text under its collation. An nvarchar parameter introduces a Unicode comparison. Data type precedence places nvarchar above varchar.

SQL Server therefore converts the lower-precedence expression when the comparison needs conversion. An indexed column can become that expression. The plan then shows CONVERT_IMPLICIT around the column.

The resulting access method depends on the collation and query. Some Windows collations support useful seeks despite this conversion. Don't claim every nvarchar parameter forces a scan under every collation.

The safe design is still to match the schema deliberately. Confirm whether the business needs Unicode first. Then use the same choice consistently across storage and application code.

Build a Lookup You Can Inspect

Use a disposable database for the sample table. The explicit SQL collation makes the conversion issue easier to inspect. It isn't a recommendation to change your production database collation.

The generated codes are sample inputs. They aren't a measured production workload. Turn on the actual execution plan in SSMS before comparing the two calls.

The index includes DisplayName so the lookup doesn't require that column from another access path. That keeps the example focused on the search predicate. Inspect both the seek predicate and any remaining filter.

CREATE TABLE dbo.ParameterTypeDemo
(
    CustomerId int NOT NULL PRIMARY KEY,
    CustomerCode varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    DisplayName varchar(80) NOT NULL
);
INSERT dbo.ParameterTypeDemo(CustomerId, CustomerCode, DisplayName)
SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY object_id),
       'C' + RIGHT('000000' + CONVERT(varchar(6), ROW_NUMBER() OVER (ORDER BY object_id)), 6),
       'Sample customer'
FROM sys.all_objects;
CREATE INDEX IX_ParameterTypeDemo_Code
ON dbo.ParameterTypeDemo(CustomerCode) INCLUDE(DisplayName);
SET STATISTICS IO ON;
EXEC sys.sp_executesql
    N'SELECT DisplayName FROM dbo.ParameterTypeDemo WHERE CustomerCode = @Code;',
    N'@Code nvarchar(20)', @Code = N'C000100';
EXEC sys.sp_executesql
    N'SELECT DisplayName FROM dbo.ParameterTypeDemo WHERE CustomerCode = @Code;',
    N'@Code varchar(20)', @Code = 'C000100';
SET STATISTICS IO OFF;

Declare Parameter Types Instead of Inferring Them

AddWithValue infers metadata from the supplied .NET value. A string becomes nvarchar, and its length influences the parameter size. That convenience hides the database contract.

The C# fragment below assumes an existing open SQL Server connection. Add the appropriate SQL client namespace used by your application. The fragment shows two commands without executing the first one.

The second command declares varchar(20), matching the sample column. Validate input length before execution. Truncating an identifier to fit a parameter isn't acceptable validation.

// C#
using var inferred = connection.CreateCommand();
inferred.CommandText = "SELECT DisplayName FROM dbo.ParameterTypeDemo WHERE CustomerCode = @Code;";
inferred.Parameters.AddWithValue("@Code", customerCode);
using var explicitCommand = connection.CreateCommand();
explicitCommand.CommandText = inferred.CommandText;
explicitCommand.Parameters.Add("@Code", SqlDbType.VarChar, 20).Value = customerCode;

Give Decimal Parameter Types Precision and Scale

A decimal parameter needs precision and scale. Precision covers total digits, while scale covers digits after the decimal point. Different inferred declarations aren't interchangeable metadata.

I inspect these values when an amount filter behaves differently across calls. A price and a ratio deserve different declarations. The column definition should settle the choice.

This C# fragment adds a decimal(12,2) parameter. The application must reject values outside the agreed range. SQL Server shouldn't become the first place you discover a precision mismatch.

A null value also needs an explicit type. Set Value to DBNull.Value while retaining the declared metadata. A missing value doesn't remove the need for a database contract.

// C#
var amountParameter = explicitCommand.Parameters.Add("@Amount", SqlDbType.Decimal);
amountParameter.Precision = 12;
amountParameter.Scale = 2;
amountParameter.Value = amount.HasValue ? (object)amount.Value : DBNull.Value;

Look for Conversion in Cached Plans

Search cached Showplan XML for CONVERT_IMPLICIT near your table. The statement text narrows the search, but it doesn't prove ownership. A shared query shape still needs application context.

The query below uses documented cache and plan functions. Server permissions are required to inspect other sessions' cached work. SQL Server 2022 and later use VIEW SERVER PERFORMANCE STATE for these statistics.

The text search is an investigation screen. It doesn't prove the conversion caused the expensive operator. Open the XML plan and identify the converted expression.

SELECT TOP (30) qs.execution_count, qs.total_logical_reads,
       qs.total_worker_time, st.text, qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE st.text LIKE N'%ParameterTypeDemo%'
  AND CONVERT(nvarchar(max), qp.query_plan) LIKE N'%CONVERT_IMPLICIT%'
ORDER BY qs.total_logical_reads DESC;

Keep Parameter Types Stable between Calls

Inferred string lengths can produce different parameter declarations for the same statement text. Those differences create additional cache entries. A stable declared size removes that avoidable variation.

Cache reuse also depends on database context and relevant SET options. Don't blame every extra plan on string lengths. Compare declarations and plan attributes before assigning a cause.

What does your application send for the shortest and longest accepted code? Check both through the real application. A handwritten SSMS test doesn't reproduce inferred client metadata automatically.

Parameter types deserve the same review as table definitions. Capture the actual declaration during an application call. Then compare it with the column's type, length, precision and scale.

A new index cannot repair every mismatched parameter contract. It can make the wrong comparison more elaborate. The server has no award for the most creative conversion.

Choose Unicode for the Business Requirement

Changing the column to nvarchar is appropriate when the business requires Unicode data. That is a schema decision with storage and index consequences. Review existing text before making it.

Changing the application to varchar fits an established non-Unicode contract. Confirm the collation's supported characters and test representative input. Don't sacrifice valid names to win a seek.

I compare plans and reads after fixing the declaration. I also test nulls, boundary lengths and unusual characters. Correctness comes before a pleasing plan icon.

Stable parameter types make the contract visible to both teams. Save that contract beside the application query. Then repeat the comparison through the connection that users use.

Review empty strings separately from nulls. They express different business values and don't share the same predicate behavior. Parameter metadata should remain stable for both accepted cases.

Large text parameters deserve another explicit decision. Use a bounded size when the schema is bounded. Use the supported maximum-length declaration only when the column and application contract require it.

A fixed size doesn't mean every value must fill that size. Short values still travel with their actual content. The declaration tells SQL Server the comparison contract, not the number of characters a user typed.

Check stored procedure parameters as well as inline statements. The procedure declaration can provide a stable server-side boundary. The application still needs appropriate types for conversion and input validation.

Related reading on this blog: Implicit Conversions That Quietly Turn Seeks Into Scans and How to Fix CONVERT_IMPLICIT Warnings?.

A parameter declaration is not application decoration, it is part of the database query contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.