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

CREATE TABLE #MaxPartitionRows
    PNumber INT 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?

1 Answer 1


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
    ( 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:

  ( 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 ;
  • Thanks for an explanation and suggested solution, somehow didn't thought about apply function. Mar 2, 2016 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.