title: Testing performance of Scalar vs Table-valued functions in sql server
body: <p>OK so I have read a whole bunch of articles suggesting table-value functions and cross apply give better performance than a scalar udf.  I wanted to write my function in both ways and then test to see which one is better - but I cannot figure out what I'm supposed to use/look for to understand which is the better option.</p>
  
  <p>I'm using SQL Server 2005.  I've tried running the estimated execution plan, the actual execution plan and analyze query in database engine tuning advisor and I don't know what it is trying to tell me.</p>
  
  <p>Using showplan_all on /off it looks like the table based function will use more cpu 1.157e-06 vs 8.3e-05 but the table function has a total subtree cost of 0.000830157 vs 0.01983356.</p>
  
  <p>The query cost of the table valued function also seems to have a higher cost than the scalar one.  Even though I thought it was supposed to be the better option.</p>
  
  <p>So whilst I'd like to prove it myself which one gives the better performance - I'm just not sure what to look for in these tools - so any suggestions would be appreciated!</p>
  
  <p>I need to get an academic year value (based on a date range set in the database) based on a calendar date so the function contents are below - so its just whether I go scalar or table based.  This year feeds into other queries.. </p>
  
  <pre><code>CREATE FUNCTION fn_AcademicYear
  (
      -- Add the parameters for the function here
      @StartDate DateTime
  )
  RETURNS 
  @AcademicYear TABLE 
  (
      AcademicYear int
  )
  AS
  BEGIN
  
  DECLARE @YearOffset int, @AcademicStartDate DateTime 
  
      -- Lookup Academic Year Starting Date
      SELECT @AcademicStartDate = CONVERT(DateTime,[Value])
      FROM dbo.SystemSetting
      WHERE [Key] = 'AcademicYear.StartDate'
  
      SET @YearOffset = DATEPART(YYYY,@StartDate) - DATEPART(YYYY,@AcademicStartDate);
      -- try setting academic looking start date to year of the date passed in
      SET @AcademicStartDate = DATEADD(YYYY, @YearOffset, @AcademicStartDate);
  
      IF @StartDate &lt; @AcademicStartDate
      BEGIN
          SET @AcademicStartDate = DATEADD(YYYY, @YearOffset-1, @AcademicStartDate);
      END
  
        INSERT @AcademicYear
        SELECT YEAR(@AcademicStartDate)
  
      RETURN 
  </code></pre>
  
  <p>Thanks!!</p>