Sql-server – Window function not working on non selected rows

sql serversql server 2014window functions

I'm creating auto switching partitions procedure.
I'm creating table that looks like this:

CREATE TABLE #MaxPartitionRows
    (MaxRows BIGINT NOT NULL,
    PNumber INT NOT NULL, 
    PFName SYSNAME NOT NULL,
    Value VARCHAR(30) NOT NULL)

I'm using it to decide whether to merge partitions or not.
The columns are: max rows in tables per partition function, partition number, partition function and boundary value.
The logic is to merge partition only if the next partition is also empty.
So I tried this query:

 SELECT @vcPartitionFunction    = PFName,
    @vcBoundaryValue        = Value,
    @NextPartitionRowNum    = LEAD(MaxRows) OVER (ORDER BY PNumber)
FROM #MaxPartitionRows
WHERE PNumber = @iMinPartitionNumberToSwitchOut

The problem that I've faced is that @NextPartitionRowNum gets NULL as a value, even if there are rows in the table with bigger partition number.
My guess is that engine don't need to scan next row so it returns NULL.
Is that the correct assumption?
In this case the workaround is simple, I just use 

SELECT @NextPartitionRowNum = MaxRows 
    FROM #MaxPartitionRows
    WHERE PNumber = @iMinPartitionNumberToSwitchOut + 1

But what if I couldn't do that?

Best Answer

LEAD() is calculated after the WHERE clause has limited the result to one row (I'm assuming that (PNumber) is unique). That's why you always get NULL for @NextPartitionRowNum.

You could use your select, only if you are sure that there are no gaps in the PNumber values:

SELECT @NextPartitionRowNum = MaxRows 
    FROM #MaxPartitionRows
    WHERE PNumber = @iMinPartitionNumberToSwitchOut + 1 ;

Alternatively, you could use this (gaps or not, doesn't affect it):

SELECT @vcPartitionFunction    = p.PFName,
    @vcBoundaryValue        = p.Value,
    @NextPartitionRowNum    = n.MaxRows
FROM #MaxPartitionRows AS p
  OUTER APPLY
    ( SELECT TOP (1) n.MaxRows
      FROM #MaxPartitionRows AS n
      WHERE n.PNumber > p.PNumber
      ORDER BY n.PNumber
    ) AS n
WHERE p.PNumber = @iMinPartitionNumberToSwitchOut ;

If you prefer window functions, this would work, too. First calculate LEAD() over the entire table, then restrict with WHERE:

WITH cte AS
  ( SELECT PFName, Value, PNumber, 
           NextMaxRows = LEAD(MaxRows) OVER (ORDER BY PNumber)
    FROM #MaxPartitionRows
  )
SELECT @vcPartitionFunction    = PFName,
       @vcBoundaryValue        = Value,
       @NextPartitionRowNum    = NextMaxRows
FROM cte
WHERE PNumber = @iMinPartitionNumberToSwitchOut ;
Related Question