How SQL Server knows when to implicitly convert the values

Question

Following my previous question: Implicit conversion does not affect performance

I used below simple query

select count(*)
from fpc
where SKey in ('201701', '201702A')

SKey is of type int and I have non clustered columnstore index on it.

obviously I cannot run this because the second value is not a number. so I hit Ctrl+L to see estimated execution plan and I see an interesting thing in Predicate property:

[mydb].[dbo].[fpc].[SKey]=CONVERT_IMPLICIT(int,'201702A',0) OR
[mydb].[dbo].[fpc].[SKey]=(201701)

My question is why SQL uses '201701' as a number but uses implicit conversion on the second value: '201702A'

what I'm interested in is the internal mechanism how SQL server looks into these two values. Does it know the first one is a number and the second one is not?

Answer 1

My question is why SQL uses '201701' as a number but uses implicit conversion on the second value: '201702A'

what I'm interested in is the internal mechanism how SQL server looks into these two values. Does it know the first one is a number and the second one is not?

SQL Server attempts to convert both strings to the correct type (according to the data type precedence rules) for comparison with the integer Skey column during the Constant Folding phase of query compilation. This activity occurs very early in the process, well before even the simplest of query plans is considered.

When constant folding is successful, the input tree contains the derived literal value (as the correct type) and optimization continues, just as if the query writer had used a constant rather than an expression.

When constant folding is unsuccessful (for example because the conversion would throw an error) the tree contains a conversion function. It would not be correct to throw an error at compilation time; an error should only occur when the query is executed, and the problematic expression is actually evaluated (if at all).

So, in your case, '201701' is constant-folded to integer 201701, but '201702A' becomes CONVERT_IMPLICIT(int,'201702A',0).

Constant folding is much more powerful and complete than the above simple example would suggest. For example:

LastName LIKE SUBSTRING(LEFT(NCHAR(UNICODE(NCHAR(68))), 1) + N'%', 1, 2)

is constant-folded to:

LastName LIKE N'D%'

In SQL Server 2012 and later, even deterministic SQLCLR scalar functions can be constant-folded.