title: Mixing indexed and calculated fields in a table-valued function
body: <p>I work with SQL Server 2008, but can use a later version if it would matter.</p>
  
  <p>I have 2 tables with pretty similar data about some people but in different formats (no intersections between these 2 sets of people).</p>
  
  <pre><code>Table 1:    
  int personID    
  bit IsOldPerson  //this field is indexed
  
  Table 2:
  int PersonID
  int Age
  </code></pre>
  
  <p>I want to have a combined view that has the same structure as the Table 1. So I write the following script (a simplified version):</p>
  
  <pre><code>CREATE FUNCTION CombinedView(@date date)
  RETURNS TABLE
  AS
  RETURN        
      select personID as PID, IsOldPerson as IOP
      from Table1
  
      union all
  
      select personID as PID, dbo.CheckIfOld(Age,@date) as IOP
      from Table2
  GO
  </code></pre>
  
  <p>The function "CheckIfOld" returns yes/no depending on the input age at the date @date.</p>
  
  <p>So I have 2 questions here:</p>
  
  <p>A. if I try <code>select * from CombinedView(TODAY) where IOP=true</code>, whether the SQL Server will do the following separately:  1) for the Table 1 use the index for the field IsOldPerson and do a "clever" index-based selection of results; 2) for the Table 2 calculate CheckIfOld for all the rows and during the calculation pick up or rejecting rows on the row-by-row basis ?</p>
  
  <p>B. how can I check the execution plan in this particular case to understand whether my guess in the question (A) is correct or not?</p>
  
  <p>Any help is greatly appreciated! Thanks!</p>