Scalar Valued functions

• WangcChiKaBastar

  SSCrazy

  Points: 2297

  More actions

  May 30, 2013 at 9:59 am

  #274375

  I have a Stored Proc that calls a Scalar Valued function...

  When I run the Scalar Valued function

  select dbo.f_ScalarFunction (@Param1, @Param2, @Param3) and check Inlude Actual execution plan..

  I see that it does a CONSTANT SCAN ... ??

  I see my underlying Queries in the function are well optimized...they do Index Seek if run in isolation.

  Question is Does Scalar Valued function not utilize the Query plan of the underlying SQL Queries ?

  Do they always do CONSTANT SCAN ?

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 30, 2013 at 10:06 am

  #1619774

  WangcChiKaBastar (5/30/2013)

  ---

  I have a Stored Proc that calls a Scalar Valued function...

  When I run the Scalar Valued function

  select dbo.f_ScalarFunction (@Param1, @Param2, @Param3) and check Inlude Actual execution plan..

  I see that it does a CONSTANT SCAN ... ??

  I see my underlying Queries in the function are well optimized...they do Index Seek if run in isolation.

  Question is Does Scalar Valued function not utilize the Query plan of the underlying SQL Queries ?

  Do they always do CONSTANT SCAN ?

  Constant Scan provides your query with in-memory table of constant values. I don't think you should worry about it. It has nothing to do with Table/Index scan.

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 30, 2013 at 10:11 am

  #1619778

  It may help to know what the function does and how it is used, can you post some code?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 30, 2013 at 10:38 am

  #1619788

  As general information, user defined scalar valued functions won't allow you to use parallellism in your queries.

  You might find better performance using an inline Scalar Valued Function as called by Jeff Moden on this article: http://www.sqlservercentral.com/articles/T-SQL/91724/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2