Sql Server Transaction Savepoint

Create Table Demo
(
DemoID Int,
DemoData varchar(100)
)

DECLARE @T1 varchar(20) = 'T1';
DECLARE @T2 varchar(20) = 'T2';
DECLARE @T3 varchar(20) = 'T3';

Begin Tran
       Insert into Demo Values (1, 'Kamlesh')
       SAVE TRANSACTION p1;
     
       Insert into Demo Values (2, 'Singh')
       SAVE TRANSACTION p2;
     
       Insert into Demo Values (3, 'Akshat')
       SAVE TRANSACTION p3;
     
             
       ROLLBACK TRAN p2;


       select * from Demo

       COMMIT TRAN
       Delete from Demo

OUTER and CROSS APPLY

Introduction 

Hiya!  This article will explain OUTER and CROSS APPLY and show you how to use them by means of sample code.  OUTER and CROSS APPLY are unique to SQL Server so this article is intended for anybody using SQL in a SQL Server environment.  It will also cover many examples of where you can use OUTER and CROSS APPLY and their pro's and con's.
Use cases in this article include:

• TOP 
• UNPIVOT 
• Multi-field expressions 
• Using expressions in other expressions 
• APPLY and TVFs

Explaining by example

Instead of giving definitions I would like to explain by example.  Think of CROSS APPLY as a row-by-rowINNER JOIN .  If we have:

SELECT *
FROM Vehicles V
INNER JOIN MileageLog ML ON V.ID = M.VehicleID  

to join a vehicle and its mileage log we could do exactly the same thing using CROSS APPLY:

SELECT * 
FROM Vehicles V
CROSS APPLY (SELECT * FROM MileageLog ML WHERE V.ID = ML.VehicleID) ML 

These two queries will produce identical results.   We could use OUTER APPLY instead of CROSS APPLY to get the same effect as a LEFT JOIN.  That is

SELECT * 
FROM Vehicles V
LEFT JOIN MileageLog ML ON V.ID = ML.VehicleID   

will give the same results as:

SELECT *
FROM Vehicles V
OUTER APPLY (SELECT * FROM MileageLog ML WHERE V.ID = ML.VehicleID) ML 

Notice how our ON condition becomes a WHERE condition in the subquery. Also notice how we give an alias for the APPLY just like we can alias tables in a JOIN statement - this is required for APPLY statements.

Use case 1: TOP N Rows 

These queries now do the same thing and the JOIN is easier to write and remember, so why on earth would we use APPLY instead?
Let's say that instead of all mileage log entries for every vehicle we now only want the last 5 entries for every vehicle.  One way of doing this is with ROW_NUMBER, PARTITION BY and a nested query:

Hide   Copy Code

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ML.VehicleID ORDER BY ML.EntryDate DESC) RN  
  FROM Vehicles V 
  INNER JOIN MileageLog ML ON V.ID = ML.VehicleID 
) IQ
WHERE IQ.RN <= 5            

Which would only return the first 5 entries for every vehicle.  To do so using a CROSS APPLY statement:

Hide   Copy Code

SELECT * 
FROM Vehicles V 
CROSS APPLY (
  SELECT TOP 5 * 
  FROM MileageLog ML 
  WHERE V.ID = ML.VehicleID 
  ORDER BY ML.EntryDate DESC) ML         

The are a few important things to take note of here:

• We can use TOP inside a CROSS APPLY statement:  Since CROSS APPLY works row-by-row it will select the TOP 5 items for every row of the Vehicles table.   
• We don't have to specify partitioning since CROSS APPLY is always row-by-row. Think of it as a built in PARTITION BY clause that is always there. 
• The ROW_NUMBER approach will add a new field where CROSS APPLY does not. 

 This allows us to do things that would normally be somewhat complex in much more expressible ways.  If we want the TOP 10 PERCENT rows without an APPLY statement it would have to be something like: 

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ML.VehicleID ORDER BY ML.EntryDate DESC) RN   
  FROM Vehicles V  
  INNER JOIN MileageLog ML ON V.ID = ML.VehicleID  
) IQ 
INNER JOIN 
( 
   SELECT ML.VehicleID, COUNT(*) AS RowCount
   FROM MileageLog ML 
   GROUP BY ML.VehicleID 
) MLCount ON IQ.VehicleID = MLCount.VehicleID 
WHERE RN / cast(MLCount.RowCount as float) <= 0.1 

As you can see this becomes a more complex query since we now require aggregates and single-row expressions in order to calculate our own percentages.  It also very quickly becomes unclear what we were trying to do.
If we use CROSS APPLY doing this is simply: 

Hide   Copy Code

SELECT *  
FROM Vehicles V 
CROSS APPLY (
  SELECT TOP 10 PERCENT * 
  FROM MileageLog ML 
  WHERE V.ID = ML.VehicleID  
  ORDER BY ML.EntryDate DESC) ML  

Are you starting to see how CROSS APPLY can make your life easier?

Use case 2: UNPIVOT 

UNPIVOT unfolds a single row into multiple rows.  The syntax for UNPIVOT works well if you're doing single table UNPIVOTs and gets rather complicated when you're joining or doing multiple.  I'm not going to cover UNPIVOT examples here for the sake of brevity - feel free to Google (or the search engine of your preference) a few examples before reading on.
If we have the following data (first row is column names) in the table tbl:
A B C D  
E 1 2 3
F 4 5 6 
We can unpivot it using a CROSS APPLY as follows:

SELECT A, Category, Value 
FROM tbl 
CROSS APPLY (
  SELECT 'B' AS Category, B AS Value UNION ALL 
  SELECT 'C', C UNION ALL 
  SELECT 'D', D
) CA  

Viola, that's it.  It will unfold the data like such:
A Category Value
E B 1
E C 2
E D 3
F B 4
F C 5
F D 6 
Which is the same results that UNPIVOT would give.
A few important things to note:

• We can use UNION ALL inside a CROSS APPLY statement to work in the same what that UNPIVOT would.  
• Performance Note:  UNPIVOT has major performance impact in various situations as many readers may be aware due to joins on its data being required later in many circumstances.  I have found situations with large unfold operations where APPLY is actually orders of magnitude faster than UNPIVOT.  This is especially true where unfolding multiple fields in a single table (hence where the apply query has no join predicates) since the row-by-row nature is often faster than joins to bring together multiple fields.  Specifically useful for systems storing multiple aggregates in single rows https://www.codeproject.com/script/Forums/Images/smiley_wink.gif   
• When we combine UNION ALL with TOP, WHERE, GROUP BY, etc we can now do interesting things whilst unfolding (like unfolding only the top 3 values that are not NULL, getting only ). 
• Remember that this is now an unfold operation which is already partitioned on a row-by-row basis - anything you unfold is combined with whatever data you already have in each row.  This can be very useful in many situations. 

Use case 3: Multi-field expressions

Lets say we want to know which day every vehicle travelled the furthest:

SELECT *, (
  SELECT TOP 1 EventDate 
  FROM MileageLog ML 
  WHERE V.ID = ML.VehicleID ORDER BY DistanceTravelled DESC) AS DayMostTravelled 
FROM Vehicles V  

Simple enough, right?  Doing this with OUTER APPLY looks like such:

SELECT * 
FROM Vehicles V
OUTER APPLY (
  SELECT TOP 1 EventDate AS DayMostTravelled
  FROM MileageLog ML
  WHERE V.ID = ML.VehicleID
  ORDER BY DistanceTravelled DESC
) CA  

Only a few small changes in the code is necessary:

• Our expression moves into a APPLY subquery outside the statement 
• Our alias is now inside the subquery 
• Our APPLY receives an alias that we do not have to directly use 
• Notice that we use OUTER APPLY and not CROSS APPLY in this scenario.  Using CROSS APPLY would have only shown rows that have MileageLog entries where OUTER APPLY will show those of all vehicles. 

So if we now want to know the date and the distance travelled on that day?

SELECT V.*, IQ.EventDate AS DayMostTravelled, IQ.DistanceTravelled 
FROM Vehicles V   
OUTER JOIN ( 
  SELECT VehicleID, EventDate, DistanceTravelled, 
    ROW_NUMBER() OVER (PARTITION BY VehicleID ORDER BY DistanceTravelled DESC) RN 
  FROM MileageLog 
) IQ ON IQ.VehicleID = V.ID AND IQ.RN = 1  

Since this is no longer a single field we now have to use JOIN and ROW_NUMBER to get our desired information.  Doing this with OUTER APPLY on the other hand:

SELECT * FROM Vehicles V
OUTER APPLY (
  SELECT TOP 1 EventDate AS DayMostTravelled, DistanceTravelled
  FROM MileageLog ML
  WHERE V.ID = ML.VehicleID
  ORDER BY DistanceTravelled DESC
) CA     

This gives us an easy way to select multiple fields from a related row based on some condition.

Use case 4: Using expressions in other expressions 

We can use CROSS APPLY to give expressions names and use them in other expressions. 

SELECT V.*, CA1.AvgDistance, CA1.TotalDistance 
FROM Vehicles V 
OUTER APPLY ( 
   SELECT Avg(DistanceTravelled) AS AvgDistance, Sum(DistanceTravelled) AS TotalDistance 
   FROM MileageLog ML 
   WHERE V.ID = ML.VehicleID
) CA1  

The query above simply gets the average and total distance travelled for each vehicle.

SELECT V.*, CA1.AvgDistance, CA1.TotalDistance, CA2.ServicesLeft
FROM Vehicles V 
OUTER APPLY ( 
   SELECT Avg(DistanceTravelled) AS AvgDistance, Sum(DistanceTravelled) AS TotalDistance 
   FROM MileageLog ML 
   WHERE V.ID = ML.VehicleID
) CA1  
OUTER APPLY (
  SELECT COUNT(*) AS ServicesLeft 
  FROM VehicleServicePlans VSP 
  WHERE VSP.VehicleID = V.ID 
    AND VSP.ServicePlanDistance > CA1.TotalDistance 
) CA2     

As you see we can add a second OUTER APPLY to now use the results of the first and do some additional calculations.   Chaining APPLYs in this way makes it easy to seperate same-row logic into multiple sections.

Use case 5: APPLY and TVFs 

APPLY also works with TVFs.
Let's say we have a TVF to get the fields of a table:

CREATE FUNCTION FieldsForTable (@tablename nvarchar(1000)) 
RETURNS TABLE 
AS 
RETURN 
  select * from sys.columns where object_id = object_id(@tablename)  

If we now want to get the fields for all tables starting with an A we can do it using CROSS APPLY:

SELECT * FROM sys.tables T CROSS APPLY dbo.FieldsForTable(T.name) 
WHERE T.name LIKE 'a%'       

Note:  This could obviously be done using a single JOIN statement - the example is exactly that and just demonstrates how to use CROSS APPLY with TVFs.

Notes on the performance of APPLY  

Since APPLY works on a row-by-row level:

• It is usually slower than JOIN due to its row-by-row nature. In many situations SQL Server's query planner will optimize APPLYs to run as if they are JOINs.    
• They will normally match the speed of using single-field expressions in a query since they act in the same manner and will be optimised similarly. 
• For "multi-field expressions" they will mostly exceed the speed of multiple single-field expressions in many scenarios since they will translate into a lower quantity of effective lookups. 
• They will match or exceed the speed of UNPIVOT statements depending on query complexity. 

Conclusion 

CROSS and OUTER APPLY can simplify many queries and provides an easier way to express many forms of logic.   It can be used to express row-by-row logic and is a very useful tool for many different situations of which a few have been illustrated.