Thursday, March 31, 2016

SQL 2012 - SUM syntax in 2012. IE: I need to do more research on the latest SQL Server versions.

So I have written SQL for a long time, and what happens sometimes is that you get entrenched in a specific way of doing things without realizing that new, better, methods can exist.  Not only are these methods new and better, they are also much faster as well (not just easier to write).

I had this happen today when someone mentioned to me that the method of doing month over month sums by year that I was doing were archaic.  This piqued my interest because that is not something I would normally have expected to hear.  

Basically, the problem is this, I have a bunch of data being summed and I want to make sure that for any specific date I have the rolling number of the previous months sums.  So if I had 10 dollars in January, 10 dollars in February, and 10 dollars in March, then by March I have a total of 30 bucks, February 20 bucks, and January is just 10.  Seems pretty straight forward, and this is a totally common task.

What I didn't realize is that in SQL Server 2012 and beyond, the code to accomplish this goal is less obtuse because functionality was added to the SUM syntax.

So I created a small example that you can review:

IF Object_id('tempdb..#tmp') IS NOT NULL 
  
BEGIN 
      
DROP TABLE #tmp 
  
END 

CREATE TABLE #tmp 
  
( 
     
id      INT IDENTITY(1, 1), 
     
mth_nb  INT, 
     
year_nb INT, 
     
value   DECIMAL(16, 2) 
  
) 

INSERT INTO #tmp 
            
(mth_nb, 
             
year_nb, 
             
value) 
SELECT '1'  MTH_NB, 
       2016 
year_nb, 
       
'10' AS value 
UNION 
SELECT '2'  MTH_NB, 
       2016 
year_nb, 
       
'10' AS value 
UNION 
SELECT '3'  MTH_NB, 
       2016 
year_nb, 
       
'10' AS value 
UNION 
SELECT '4'  MTH_NB, 
       2016 
year_nb, 
       
'10' AS value 
UNION 
SELECT '5'  MTH_NB, 
       2016 
year_nb, 
       
'10' AS value 
UNION 
SELECT '1'  MTH_NB, 
       2015 
year_nb, 
       
'1'  AS value 
UNION 
SELECT '2'  MTH_NB, 
       2015 
year_nb, 
       
'1'  AS value 
UNION 
SELECT '3'  MTH_NB, 
       2015 
year_nb, 
       
'1'  AS value 
UNION 
SELECT '4'  MTH_NB, 
       2015 
year_nb, 
       
'1'  AS value 
UNION 
SELECT '5'  MTH_NB, 
       2015 
year_nb, 
       
'1'  AS value 

-- The sql server 2008 method 
SELECT ta.*, 
       
tart.runningtotal 
FROM   #tmp ta 
       
OUTER apply (SELECT year_nb, 
                           
Sum(value) AS RunningTotal 
                    
FROM   #tmp tsub 
                    
WHERE  ta.year_nb = tsub.year_nb 
                           
AND ta.mth_nb >= tsub.mth_nb 
                    
GROUP  BY year_nb) tart 
ORDER  BY ta.year_nb, 
          
mth_nb 

-- sql server 2012 method 
SELECT *, 
       
Sum(value) 
         
OVER( 
           
partition BY year_nb 
           
ORDER BY mth_nb) RunningTotal 
FROM   #tmp

The output of both is the correct answer!  But the SQL Server 2012 method is far faster!

id
MTH_NB
year_nb
value
RunningTotal
1
1
2015
1
1
3
2
2015
1
2
5
3
2015
1
3
7
4
2015
1
4
9
5
2015
1
5
2
1
2016
10
10
4
2
2016
10
20
6
3
2016
10
30
8
4
2016
10
40
10
5
2016
10
50


Here is the MSDN article on SUM.

https://msdn.microsoft.com/en-us/library/ms187810.aspx

NOTE: Even though the MSDN article says it starts in 2008, I try to run the new syntax on my  2008 instance and it doesn't work.