title: SQL Server 2008R2 User Defined Function (Table valued) Performance
body: <p>I am using SQL Server 2008R2.</p>
  
  <p>I've got the following setup:</p>
  
  <pre><code>-- Query #1
  SELECT * FROM
  Product P
  INNER JOIN ProductComments C ON C.ProductId = P.ProductId
  
  -- Query #2
  SELECT * FROM 
  GetAllProducts() P
  CROSS APPLY GetCommentsOfProduct(P.ProductId) C
  </code></pre>
  
  <p>where <code>GetAllProducts()</code> is</p>
  
  <pre><code>CREATE FUNCTION GetAllProducts
  (      
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
      SELECT * FROM Product
  )
  </code></pre>
  
  <p>and <code>GetCommentsOfProduct(P.ProductId)</code> is</p>
  
  <pre><code>CREATE FUNCTION [dbo].[GetCommentsOfProduct]
  (   
      @ProductId int
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
      SELECT * FROM ProductComments WHERE ProductId = @ProductId
  )
  </code></pre>
  
  <p>Both query #1 and query #2 result in the exact same actual execution plan and return the same result. Their query costs are both 50% relative to batch. Does this mean, that the SQL query optimizer translates these queries into the same query? And why is there no overhead for calling the UDFs? And does it mean I can structure all of my tables into UDFs and will have no performance issues even when doing JOIN operations? Do you know of any downsides?</p>