Changes to the OVER clause
RANGE / ROWS clause
The new ROWS keyword in SQL Server 2012 expression allows us to specify the window frames over which our expressions will be evaluated.
The following ROWS clause, for example, allows us to restrict a calculation to all previous rows in a dataset for the defined partition and the provided ORDER BY clause
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Note that CURRENT ROW actually means all rows in the current partition that have the same ordering value (as defined by the ORDER BY clause) as the current one.
Self joins are no longer required to calculate running values.
As an example, the following query will return an event history from an event list. I considered a CRM system where cases/incidents/tickets can be created, closed and reopened. The raw event list should be transformed into an event history, displaying for each incident the periods over which it was opened.
WITH EventList AS (
(1, ‘NEW’, CAST(‘2012-01-04 15:00:00’ AS datetime))
,(1, ‘CLOSE’, CAST(‘2012-01-04 15:37:00’ AS datetime))
,(2, ‘NEW’, CAST(‘2012-01-05 15:00:00’ AS datetime))
,(2, ‘CLOSE’, CAST(‘2012-01-06 15:00:00’ AS datetime))
,(2, ‘REOPEN’, CAST(‘2012-01-07 15:00:00’ AS datetime))
,(2, ‘CLOSE’, CAST(‘2012-01-09 15:00:00’ AS datetime))
,(1, ‘REOPEN’, CAST(‘2012-01-18 15:00:00’ AS datetime))
AS DATA(ID, EventCode, EventDate)
, EventDate As StartDate
, MIN(CASE WHEN EventCode = ‘CLOSE’ THEN EventDate END)
OVER (PARTITION BY ID ORDER BY EventDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Nice and simple.
A questionable design decision
Due to a questionable design decision, the behavior of the new extended OVER clause may be counter-intuitive.
Consider the expression:
COUNT(EventDate) OVER (PARTITION BY ID)
This gives you the number of rows for the current ID.
The following expression is now legal in SQL Server 2012 – earlier versions would have thrown an error (Incorrect syntax near ‘order’).
OVER (PARTITION BY ID ORDER BY EventDate )
Since COUNT is an additive function – this implies the order of evaluation does not matter – I expected this expression to return exactly the same results. Furthermore, I expected this expression to return exactly the same result independently of the field specified in the ORDER BY clause.
However, if you use such an expression , you will find out SQL Server actually restricts the partition depending on the fields listed in the ORDER BY clause. The MSDN documentation is quite clear about this ‘If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame.’
If I provide no ORDER BY or ROWS/RANGE clause, SQL Server will assume RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Why should I expect a different behavior, when I only explicitly modify the ORDER BY clause?