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):
USE DBName 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