November 17, 2013 at 9:57 pm
Hi Experts,
This is kind of wired behaviour on part of SQL Server.
1. I have a query whose execution plan initially showed the used of expensive CONVERT_IMPLICLIT operator
2.Then i change the where clause to accomodate the CAST operator so that CONVERT_IMPLICLIT would not be used
3.I run the query but still it was using CONVERT_IMPLICLIT operator.
4.I then clear the cache using DBCC command (in case it was using previous generated query plan)
5.I run again the same query but to my surprise ; execution plan still uses CONVERT_IMPLICLIT operator
Has anybody faced this situation. Could anybody would be help me with this
November 17, 2013 at 10:11 pm
Post the plan.
Not seeing why you're concerned though. Convert implicit isn't expensive, it just prevents index seeks, and cast does exactly the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2013 at 1:52 pm
I'd say that it depends on where the convert_implicit is. If it is converting a column used in the join or where clause, it could have a much bigger impact than explicitly converting the constant value on the other side of the criteria.
But as for adding the CAST and it still doing a convert_implicit, perhaps you put the cast in the wrong place. Or perhaps you didn't cast it to the correct data type definition and it has to do further conversion.
November 25, 2013 at 4:54 am
Not sure if this is what you are referring to...
SQL Server apparently treats CAST as CONVERT:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply