Implicit vs Explicit Conversion, Data-Type Precedence, SARGability, and Conversion Pitfalls

Learning outcomes

A ServiceHub query looks logically simple: find one work order by a code. After an application update, CPU rises and the plan begins scanning an indexed table. No index was dropped. The only change is parameter metadata: the application now sends Unicode text to a varchar key. This lesson connects SQL Server’s type-precedence rules to correctness and access paths, while avoiding the equally dangerous slogan that “every implicit conversion is slow.”

1. Precedence decides conversion direction

When SQL Server combines expressions of different types, the lower-precedence type is converted toward the higher-precedence type when an implicit conversion exists. The current precedence list places int above character types and nvarchar above varchar. This means int_column = '42' can convert the string literal/parameter to integer, while varchar_column = N'ABC' can require conversion toward Unicode.

SELECT    SQL_VARIANT_PROPERTY(1 + CAST(2 AS bigint), 'BaseType') AS integer_result_type,    SQL_VARIANT_PROPERTY(CAST(1 AS decimal(10,2)) + CAST(2 AS int), 'BaseType') AS decimal_result_type;GOSELECT    CASE WHEN 42 = '42' THEN 'equal' ELSE 'different' END AS numeric_text_comparison;GO-- This raises a conversion error because int has higher precedence than varchar.-- SELECT 1 WHERE 42 = 'not-an-integer';GO

Implicit conversion is not automatically a problem. Converting a constant/parameter once can be cheap and preserve an index seek. The important question is what expression gets converted and how that affects the predicate the optimizer can use.

2. Build a repeatable indexed lookup probe

The existing ServiceHub dataset is intentionally small. To make plan differences visible without polluting it, create a disposable lookup table with 5,000 rows and a clustered primary key on varchar(16). This is still a tiny lab, so treat the observed plan/reads as local evidence, not a production benchmark.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab03') IS NULL EXEC(N'CREATE SCHEMA lab03 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab03.CodeLookup;GOCREATE TABLE lab03.CodeLookup(    code varchar(16) NOT NULL CONSTRAINT PK_CodeLookup PRIMARY KEY,    payload char(100) NOT NULL);GOWITH n AS(    SELECT TOP (5000)           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn    FROM sys.all_objects AS a    CROSS JOIN sys.all_objects AS b)INSERT lab03.CodeLookup(code, payload)SELECT    CONCAT('C', RIGHT(CONCAT('000000', rn), 6)),    REPLICATE('x', 100)FROM n;GOUPDATE STATISTICS lab03.CodeLookup WITH FULLSCAN;GOSELECT COUNT(*) AS row_count FROM lab03.CodeLookup;GO

3. Compare type-aligned and type-mismatched predicates with evidence

First query the key with a varchar parameter whose type and length match the column. Then repeat with an nvarchar parameter. In many common collations/plans, the Unicode mismatch can introduce a CONVERT_IMPLICIT around the varchar column and can affect seekability. Exact plan behavior is not guaranteed across every collation and optimizer build, which is why the exercise requires observation.

USE ServiceHubLab;GOSET STATISTICS IO ON;GODECLARE @code_v varchar(16) = 'C004321';SELECT code, payloadFROM lab03.CodeLookupWHERE code = @code_v;GODECLARE @code_n nvarchar(16) = N'C004321';SELECT code, payloadFROM lab03.CodeLookupWHERE code = @code_n;GOSET STATISTICS IO OFF;GO

In SSMS enable the Actual Execution Plan, or inspect plans with another supported tool. Look for the access operator (seek versus scan), the predicate, warnings, and CONVERT_IMPLICIT. Compare logical reads from your session. The correct conclusion is “this parameter metadata changed the observed access path on this lab,” not “nvarchar is bad.” If the column is semantically Unicode, redesign the column; if it is intentionally varchar, bind a matching parameter.

4. SARGability is about search arguments, not moral judgments about functions

A predicate is commonly called SARGable when the optimizer can use it as a search argument for an index access path. Wrapping the indexed column in a scalar expression often hides the original ordering from a direct seek. But “functions are slow” is an inaccurate rule: functions on constants/parameters can be harmless, and the optimizer can simplify some expressions.

USE ServiceHubLab;GO-- The column is transformed for every candidate row.SELECT work_order_id, opened_atFROM ops.WorkOrderWHERE CONVERT(date, opened_at) = '2026-08-10';GO-- Preserve the column and express the same day as a half-open range.DECLARE @d date = '2026-08-10';SELECT work_order_id, opened_atFROM ops.WorkOrderWHERE opened_at >= @d  AND opened_at < DATEADD(day, 1, @d);GO

The range form is robust for timestamps because it does not depend on “end of day” fractional precision. If an index exists on opened_at, it gives the optimizer a directly ordered predicate. Again, verify the plan when performance matters.

5. Explicit conversion makes intent visible but does not guarantee performance

CAST/CONVERT are valuable when you need a specific target type, format style, precision, or overflow boundary. Explicit conversion can also move work to the parameter/value side so the indexed column remains untouched. But writing CONVERT is not a tuning spell: converting the column explicitly can be just as non-SARGable as an implicit column conversion.

USE ServiceHubLab;GODECLARE @raw_code nvarchar(50) = N'C004321';DECLARE @typed_code varchar(16) = CONVERT(varchar(16), @raw_code);SELECT code, payloadFROM lab03.CodeLookupWHERE code = @typed_code;GODECLARE @raw_id nvarchar(30) = N'1001';DECLARE @typed_id bigint = TRY_CONVERT(bigint, @raw_id);SELECT work_order_id, statusFROM ops.WorkOrderWHERE work_order_id = @typed_id;GO

Application code should usually bind the correct type before the SQL is sent, rather than converting every parameter inside T-SQL. The server-side pattern is useful at explicit ingestion boundaries where raw text is intentionally being parsed.

6. Deliberately wrong approach: index every slow-looking conversion

An operator sees CONVERT_IMPLICIT in a plan and immediately adds another index. This may add write/storage cost while leaving the root cause—the parameter/column type mismatch—untouched. Another operator rewrites every function in the query without checking whether it was on a constant or whether the plan changed at all.

7. Hands-on lab: prove one conversion regression and repair it

Use the lab03.CodeLookup table above. Capture an actual plan and I/O for the matching varchar parameter, the nvarchar parameter, and the repaired typed parameter. Record your collation and engine build because implicit character conversion behavior is collation-sensitive.

SELECT    SERVERPROPERTY('ProductVersion') AS engine_version,    DATABASEPROPERTYEX(N'ServiceHubLab','Collation') AS database_collation;GOUSE ServiceHubLab;GOSELECT    c.name, TYPE_NAME(c.user_type_id) AS type_name,    c.max_length, c.collation_nameFROM sys.columns AS cWHERE c.object_id = OBJECT_ID(N'lab03.CodeLookup')ORDER BY c.column_id;GO

Verification checklist

• You capture parameter types, not only visible string values.
• You compare actual/estimated plan operators and logical reads locally.
• You can point to which side of the predicate is converted.
• You can explain why CONVERT(date, opened_at) and a date range are not equivalent access-path expressions.
• You do not claim a universal speedup from one 5,000-row lab.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab03.CodeLookup;GO

8. Production judgment and next bridge

Type compatibility is part of query design and API design. Review column types together with stored-procedure parameter definitions and driver parameter metadata. In incident analysis, capture plan XML/Query Store evidence before changing schema. Prefer domain alignment over compensating indexes.

Lesson 4 turns conversion from a performance hazard into a controlled data-quality tool: CASE, COALESCE, NULLIF, and TRY_CONVERT can express resilient parsing, but their result types and evaluation rules still need deliberate handling.

Authoritative references

• Data type precedence — current precedence order, including SQL Server 2025 json placement
• CAST and CONVERT — explicit conversion syntax and style behavior
• TRY_CONVERT — error-tolerant conversion semantics
• Data types (Transact-SQL) — column/parameter type reference
• SQL Server 2025 build versions — servicing baseline for reproducibility