April 28, 2016 at 1:43 am
I am new to partitioning.
We have transient data that needs to be kept for a specified number of days, and then deleted.
To make the deletes easier, we are partitioning the table, and deleting the oldest partition daily.
Is there a way for me to check which partition a specific record is on?
I could just drop the partition, and see if the data is still there, but I want to be able to do some non-destructive validation.
April 28, 2016 at 2:50 am
April 28, 2016 at 3:27 am
MadAdmin (4/28/2016)
your partitioning function will tell you how the data is split.You can find that under storage in that dataase.
Thanks MadAdmin
I could use something like this
SELECT mt.*, p.partition_number
FROM MyTableName AS mt
CROSS APPLY (
SELECT partition_number = MIN(p.partition_number)
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN sys.partition_functions AS f ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = 'MyTableName' AND i.type <= 1
AND r.value > mt.UTCDateCreated
) AS p
However, I was hoping to be able to use something like this
SELECT mt.*, partition_number = <<SOME BUILTIN SQL FUNCTION>>
FROM MyTableName AS mt
April 28, 2016 at 5:15 am
you can use these constructs to deal with partitions
select $partition.partitionfunctionname(partitioncolumn) as partitionnumber
,*
from mypartitionedtable
select *
from mypartitionedtable
where $partition.partitionfunctionname(partitioncolumn) = 5
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply