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?