Cross Apply and Outer Apply

 I introduce one of feature of Sql server 2005 (Cross Apply and Outer Apply)  :

• We are going to create one function which will return top row from Work order table  based on  workorderFormatId and row number.  

• Now We can run following Query :
• It will return on top 2 rows from  Workorderformate 1
• select * from dbo.fn_GetMax_WorkorderNumber(1,2)
  Now We Closer Look of Cross Apply: 
  
  
  
  

   SELECT  p.WorkorderNumberFormat,

          p.WorkorderNumberSample,

          p.IsActive

  FROM    dbo.SC_SVL_Workorder AS p CROSS APPLY

       dbo.fn_GetMax_WorkorderNumber(p.WorkorderNumberFormatId, 2)

          

  WHERE   p.WorkorderNumberFormatId IN (1, 2, 3, 531, 706)

  ORDER BY p.WorkorderNumberFormatId ASC
  
  OUTER APPLY :
  
  
  

  SELECT  p.WorkorderNumberFormat,

          p.WorkorderNumberSample,

          p.IsActive

  FROM    dbo.SC_SVL_Workorder AS p OUTER APPLY

       dbo.fn_GetMax_WorkorderNumber(p.WorkorderNumberFormatId, 2)

          

  WHERE   p.WorkorderNumberFormatId IN (1, 2, 3, 531, 706)

  ORDER BY p.WorkorderNumberFormatId ASC
  
  
  •    As soon as you run above query, you will get 8 rows. Two rows for each workorderformateID (1,2,3,531). You will not get any row for workorderformateID 706 as it is not available in Workorder table.  This proves that CROSS APPLY clause works like INNER APPLY.  
  • 
    
  • you will get records for 706 WorkorderformatId IN Outer Apply Query