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

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
| improve this answer | |
  • Thank you, Scott! That works like a dream. :) – Ryan Oct 17 '18 at 7:58
  • @Ryan - you're welcome, glad it worked for you! – Scott Hodgin Oct 17 '18 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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