Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Execution plan uses CONVERT_IMPLICLIT , But i have mention CAST in where clause Expand / Collapse
Posted Sunday, November 17, 2013 9:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, September 19, 2015 9:18 AM
Points: 298, Visits: 766
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

Post #1515062
Posted Sunday, November 17, 2013 10:11 PM



Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 43,068, Visits: 40,060
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
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1515064
Posted Sunday, November 24, 2013 1:52 PM



Group: General Forum Members
Last Login: Thursday, September 10, 2015 4:30 PM
Points: 1,625, Visits: 1,582
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.

My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Principal Database Architect (consultant) at DB Best Technologies
Post #1517121
Posted Monday, November 25, 2013 4:54 AM


Group: General Forum Members
Last Login: Thursday, August 27, 2015 7:24 AM
Points: 14, Visits: 93
Not sure if this is what you are referring to...

SQL Server apparently treats CAST as CONVERT:
Post #1517198
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse