question

bobmcc avatar image
bobmcc asked

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.
parametersfunctionsuser-defined-functionconversion
2 comments
10 |1200

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

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 Likes 0 ·
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 Likes 0 ·
Dave_Green avatar image
Dave_Green answered
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

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

Grant Fritchey avatar image
Grant Fritchey answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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