Apply Operator

One of major features that I've been fascinated by in SQL Server 2005 has been the APPLY operator. This article discusses on some of the real time scenarios where you can make use of this feature. 

Scenario 1 - Running Total of a field

Consider the case where you need a running total to be calculated for a particular field. Here you can use the apply operator to calculate running total and return with each row.See below for illustration


declare @tbl table(Product_ID int identity(1,1),
Product_Name varchar(100)
)insert @tbl (Product_Name)select 'Boots' union allselect 'Peter England' union allselect 'Carona' union allselect 'Marks & Spencers'declare @tbl_items table(Item_ID int identity(1,1),
Product_ID int,
Item_Date datetime,
Price numeric(10,2)
)insert into @tbl_items (Product_ID,Item_date,Price)select 1,'20 dec 2009',150 union allselect 2,'13 Apr 2009',908 union allselect 1,'5 Mar 2009',1974 union allselect 3,'05 jan 2010',647 union allselect 2,'13 Jun 2009',832 union allselect 3,'19 May 2008',413 union allselect 3,'13 May 2009',235 union allselect 1,'23 Oct 2009',630 union allselect 1,'30 Aug 2009',110
select Product_Name,Item_date,Price, Totalfrom @tbl tjoin @tbl_items tion ti.Product_ID=t.Product_IDcross apply (select sum(Price) as Total
from @tbl_items
where Product_ID=ti.Product_ID
and Item_date<=ti.Item_date) t1Order by t.Product_ID,Item_Date

Scenario 2 - Get most recent or most ancient related data in case of 1-to -n relationship

Consider the case where you have one to many relationship existing between two tables and you want latest/earliest related detail from second table for every row of first table. In this case of you can use apply operator to achieve the result.

As an example using sample data from top, you can use below query to get latest sale detail of each product

select Product_Name,Item_date,Pricefrom @tbl tcross apply (select top 1 Item_date,Price
from @tbl_items
where Product_ID=t.Product_ID
Order by Item_date desc) t1Order by t.Product_ID

Result

Scenario 3 - Get concatenated string list of related field

Consider the case where you need to return a comma separated value of field which is related to master table field. Here also you can use apply operator coupled with for xml path to get the related values as csv list.

DECLARE @Student table(Student_ID int identity(1,1),Student_name varchar(100)
)INSERT @Student (Student_Name)SELECT 'Ram' union allSELECT 'Sita' union allSELECT 'Murali' union allSELECT 'Siddharth' union allSELECT 'Jacob'declare @Subjects table(Subject_ID int identity(1,1),
Subject_Name varchar(100)
)INSERT @Subjects (Subject_Name)SELECT 'Maths' union allSELECT 'English' union allSELECT 'Biology' union allSELECT 'Physics' union allSELECT 'Chemistry' union allSELECT 'Hindi'DECLARE @Student_subjects table(ID int identity(1,1),Student_ID int,
Subject_ID int
)INSERT INTO @Student_Subjects (Student_ID,Subject_ID)SELECT 1,1 union allSELECT 3,2 union allSELECT 4,1 union allSELECT 4,3 union allSELECT 4,5 union allSELECT 2,1 union allSELECT 3,1 union allSELECT 2,3 union allSELECT 1,3 union allSELECT 2,4 union allSELECT 5,3
SELECT s.Student_Name,left(t.u,len(t.u)-1)FROM @Student scross apply (select sub.Subject_name + ','
from @Subjects sub
inner join @Student_subjects ss
on ss.Subject_ID=sub.Subject_ID
where Student_ID=s.Student_ID
for xml path(''))t(u)

Scenario 4 - Call a table valued UDF passing each row value to do some processing

Consider the case where you need to parse a comma separated value in a field stored in a table field. Here also you can use apply operator to pass values to udf and return the individual values delimited by comma for each row of table.