3

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?

10

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.

-1

Lemme try again,

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

So in this example,Skey is int,so optimizer is smart enough to detect that '201701' is int and convert it to int only (or similar data type if it can) without using implicit conversion,because execution plan task is to execute query in best possible manner.

Optimizer detect that '201702A' is not a int so it try to convert it into int.

Take another example,

create table #t (col bigint)
insert into #t values (1),(2)
create nonclustered index b_i on #t(col)
declare @i int=1
-- first Select
select * from #t where col in(2,'3')
-- Second Select
select * from #t where col in(@i)
drop table #t

Notice my datatype is BIGINT

First example will perform similar your example.Since parameter are hardcoded,optmizer will convert '3' into int without using implicit_conversion in exection plan

In second example we that @i is int and column is Bigint,here we are trying to use small data type against big data type so

optimizer will use implicit_conversion in query plan.

Also in Second case there will be still Index Seek which prove that not all implicit_conversion is bad.

Still it is very good practice to keep similar data type in mind.

now suppose If Column was int and @i was bigint then

i) we are using big data type against small data type.

ii) At the same time optimizer is smart enough to detect that parameter is int type.

so keeping both point in mind,optimizer won't use any conversion.

Further reading

Data type precedence

  • 2
    "the main task of Execution plan is to give best possible plan" - it isn't really like that as getting the best or even close to generically would be computationally impractical. It's job is to try give out a good enough plan in a short amount of time. You can state what the best route is for a significant number of simple scenarios and they can often be combined to serve a more complex whole, so that does sometimes (especially in simple cases) equate to giving out the best plan possible. But I'm nit-picking more than a bit here... – David Spillett Apr 16 '18 at 14:05
  • @DavidSpillett , Thank for pointing my mistake. Said statement has nothing to do with main question directly.I am not so expert in language. I have edited my answer and rest of the thing that I wrote is in my own language and understanding. I am learning day by day.I think rest of the content is OK if not best. – KumarHarsh Apr 17 '18 at 3:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy