Logical Order Is Not Execution Order: A Conversion Error WHERE Missed

The bad text row is outside your filter, yet CAST still raises an error. A conversion error like this shows why the written order of SQL clauses is not an execution schedule.

Reproduce the Conversion Error

Put valid and invalid number strings into a small table, then filter for valid-looking rows while converting the value. The first query below returns ids 1 and 3 on my test server. The plan scans the clustered index and checks kind before CAST sees the bad row. The later queries in this post use the same dbo.MixedValues table, so run this block first.

CREATE TABLE dbo.MixedValues (id int NOT NULL PRIMARY KEY, kind char(1) NOT NULL, value_text varchar(20) NOT NULL);
INSERT dbo.MixedValues VALUES (1,'N','123'),(2,'X','not a number'),(3,'N','456');
CREATE INDEX IX_MixedValues_value_text ON dbo.MixedValues (value_text);

SELECT id
FROM dbo.MixedValues
WHERE kind = 'N' AND CAST(value_text AS int) > 100;

The next query is meant to fail. It is the same query with an index hint standing in for a plan change. The narrow index holds value_text but not kind, so CAST runs on every index row before the kind check. SQL Server sends ids 1 and 3, then stops with Msg 245: conversion failed when converting the varchar value 'not a number' to data type int. Nothing in the WHERE text changed. Only the access path did.

SELECT id
FROM dbo.MixedValues WITH (INDEX (IX_MixedValues_value_text))
WHERE kind = 'N' AND CAST(value_text AS int) > 100;

The written order of a predicate does not force physical evaluation order. I inspect the actual plan and its Compute Scalar and Filter operators when this appears in production.

See the Optimizer's Freedom

SQL describes a result, and the optimizer chooses a physical plan. It can push predicates, compute expressions, and reorder operations when it believes the result is equivalent under normal values. A conversion that throws makes those choices visible. I do not try to fix this by nesting the SELECT in a subquery and hoping the optimizer respects the parentheses. Simple views and common table expressions are usually inlined. An ORDER BY added for protection is equally misleading.

The execution plan can show CONVERT_IMPLICIT or CONVERT in a predicate or compute expression. Check which column is converted and whether the conversion also prevents an index seek. The runtime error is the immediate problem; extra reads can be a second one.

Use a Nonthrowing Conversion

TRY_CONVERT returns NULL when a value cannot be converted to the requested type, subject to conversions that are explicitly disallowed. Filter on the converted result when only valid numeric rows should remain. Convert once with CROSS APPLY for readable code, then select the value you checked. This expresses the actual requirement: accept rows whose text can become an integer. It does not depend on an optimizer promise about the order of two separate predicates.

SELECT m.id, c.number_value
FROM dbo.MixedValues AS m
CROSS APPLY (VALUES (TRY_CONVERT(int, m.value_text))) AS c(number_value)
WHERE m.kind = 'N' AND c.number_value IS NOT NULL;

Know What CASE Can and Cannot Promise

A CASE expression can guard a row-level conversion when the conversion is inside the selected branch. It is clearer than a filter that merely looks protective. However, SQL Server documents cases where expressions such as aggregates are evaluated before CASE receives their result. Do not stretch a CASE guard into a universal evaluation-order rule. Keep the conversion close to the condition, use TRY_CONVERT when malformed input is expected, and test the actual query shape. I have seen a defensive CASE become difficult to understand after more branches were added; the underlying text column remained the real problem.

SELECT id,
       CASE WHEN TRY_CONVERT(int, value_text) IS NOT NULL
            THEN CONVERT(int, value_text)
            ELSE NULL END AS number_value
FROM dbo.MixedValues;

Ask whether invalid values should be discarded, reported, or corrected. Returning NULL silently can hide bad input if the business expects an error. Put a data-quality query beside the safe query.

Report the Rows Behind the Conversion Error

Find values that fail conversion, excluding intended blanks or NULL according to the contract. Count them, sample them, and identify their source. Do not update production values just because they look wrong in a grid. A comma, currency symbol, or locale-specific format can be meaningful to the source system. Define a parsing rule before cleanup. If a column is conceptually numeric, store it as a numeric type after validation at ingestion. That makes every later query simpler and lets SQL Server enforce the rule.

SELECT id, value_text
FROM dbo.MixedValues
WHERE value_text IS NOT NULL
  AND TRY_CONVERT(int, value_text) IS NULL;

Make the Data Contract Explicit

If a column represents a number, define accepted characters, range, sign, and decimal rules at ingestion. A value such as "1,234" can be valid to a person but invalid to TRY_CONVERT(int, value_text). A value like "0012" can be meaningful as an identifier but should remain text. I ask the source owner which interpretation applies before cleaning rows. This prevents a quick conversion fix from turning account codes into numbers and losing leading zeros. Use a staging table to preserve the raw value and store the parsed value in a properly typed column.

The cleanup query should report invalid rows by source and import batch. One bad source can account for most failures. Fixing that source is more durable than adding another CASE expression to every report. I also test the parser under the actual database collation and language settings, especially for dates and decimal punctuation. A typed parameter or typed staging column makes the later query simpler and gives the optimizer useful statistics.

Separate Conversion Error Avoidance From Correctness

TRY_CONVERT prevents the unexpected exception, but a NULL result can be silently dropped by a WHERE predicate. Decide whether invalid input should fail the load, enter a quarantine table, or be excluded from a report with a visible count. I keep an invalid-row query next to the reporting query so the team can see what was omitted. A report that runs successfully while skipping bad data is not automatically correct.

What if the query is part of an UPDATE? Filtered conversion must not cause the update to touch the wrong set of rows. Test with valid, invalid, blank, and NULL values, then compare the intended keys. I check actual plans for implicit conversions on joins as well as explicit CAST expressions. A conversion on the indexed column can create a scan even after the runtime error is removed. The final review should show both safe behavior and acceptable work.

Test More Than One Plan

Run the query with representative data and parameter values. Check a plan that scans, one that seeks if applicable, and a case with many invalid rows. A query that happens to work after adding an index is not fixed if its correctness depends on that index being chosen. I compare the safe query's result with the intended valid rows, then review logical reads and warnings. Correctness comes before tuning.

The durable fix is a typed column or a conversion written so bad data cannot throw unexpectedly. A WHERE clause that happens to run first in the current plan is a fragile agreement with an optimizer that never signed it.

Related reading on this blog: Logical Processing Order of the SELECT Statement and How to Fix CONVERT_IMPLICIT Warnings?.

Logical query order is not an execution guarantee, it is a way to describe the result.

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