Version: SQL Server 2016

So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. What I need is a way to select the latest partition within the table that actually contains data. Does anyone have any ideas.

There's a new partition created daily, but data doesn't always get inserted within the day. There's thousands of partitions that span many years so simply scrolling through each partition isn't ideal at all.

This is how I'm currently selecting the most recent partition (or should I say the max partition):

DECLARE @PartitionValue int
set @PartitionValue = (select max(boundary_id) from sys.partition_range_values)
SELECT * FROM schema.tablename
WHERE $PARTITION.partition_function_name(expression) = @PartitionValue

1 Answer 1


Try something like this (replace schema.tablename with your object name). It worked for me in my limited testing.

DECLARE @PartitionValue INT

SET @PartitionValue = (
        SELECT TOP 1 partition_number
        FROM sys.dm_db_partition_stats
        WHERE object_id = object_id('schema.table_name')
            AND row_count > 0
        ORDER BY partition_number DESC

SELECT count(*)
FROM schema.table_name
WHERE $PARTITION.pf_schema_table_name_data(expression) = @PartitionValue
  • Thank you, Scott! That works like a dream. :)
    – Ryan
    Commented Oct 17, 2018 at 7:58
  • @Ryan - you're welcome, glad it worked for you! Commented Oct 17, 2018 at 8:52

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.