Why don't I see an implicit conversion in query plan?

Question

question

bobmcc asked Sep 13, '12

Why don't I see an implicit conversion in query plan?

I was looking through a stored procedure that one of our developers was using and I noticed he was passing a VARCHAR() value to a user defined function expecting an INTEGER. I looked at the query plan on this and expected to see an implicit conversion somewhere. Am I missing it? Is done behind the scenes? The value is clearly used as an integer. i.e. the user is passing @val1 to the UDF. @val1 is defined as DECLARE @VAL1 VARCHAR(50) = '1234'. Inside the UDF @val1 is used in a math computation.

parameters functions user-defined-function conversion

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bobmcc commented · Sep 14, 2012 at 03:18 PM

Thanks for the quick responses. I'm reading through them now. to provide more information by way of an example: If I execute the following function with select bobsFunction('50'); I would expect to see an implicit conversion of '50' to type INTEGER. CREATE FUNCTION bobsFunction ( @Param1 INTEGER ) RETURNS INTEGER AS BEGIN SET @param1 = @Param1 * 50; RETURN @Param1 END;

0 ·

Grant Fritchey ♦♦ commented · Sep 14, 2012 at 03:23 PM

Are you looking at the properties of the operator within the execution plans? If so, you can see the implicit conversion occurring there. Unless you're hitting the edge cases defined by Paul.

0 ·

Answer 1 · 2012-09-14T08:48:24Z

Dave_Green answered Sep 14, '12

Hi @bobmcc, There's a good blog post on this area by Paul White at [ http://sqlblog.com/blogs/paul_white/archive/2011/07/19/join-performance-implicit-conversions-and-residuals.aspx][1] - he doesn't have a solution, but does run through the circumstances where it can be invisible in the query plan. Whilst you don't appear to be using 2 data types in the same family, that post may help to shed some light on the matter for you. [1]: http://sqlblog.com/blogs/paul_white/archive/2011/07/19/join-performance-implicit-conversions-and-residuals.aspx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2012-09-14T11:49:56Z

Grant Fritchey answered Sep 14, '12

A UDF? Is it a multi-statement table value user defined function? If so, there's a second execution plan within the system for that. You'll need to search for that within the cache to see the plan. I have an example of how to do that in a query [on this blog post][1]. [1]: http://www.scarydba.com/2012/03/26/execution-plan-for-a-user-defined-function/

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question