紀錄一些常用語法
查詢特定某一筆資料被歸類在哪一組partition number
SELECT top 100
$PARTITION.PF_DAY(CreateTime) AS [Partition Number] , *
FROM 你的資料表名稱
where Id = 153190750
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='你的資料表名稱';
GO
SELECT p.partition_id , O.name TableName, fg.name FileGroup, ps.name PartitionScheme,pf.name PartitionFunction, ISNULL(prv.value,'Undefined') RangeValue,p.rows
FROM sys.objects O
INNER JOIN sys.partitions p on P.object_id = O.object_id
INNER JOIN sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
INNER JOIN sys.data_spaces ds on i.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf on ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
INNER JOIN sys.allocation_units au on p.hobt_id = au.container_id
INNER JOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id
WHERE o.name = '你的資料表名稱' AND i.type IN (0,1) AND p.partition_id = 72057594163298304
ORDER BY O.name, fg.name, prv.value
刪除特定某一組partition裡面的資料
TRUNCATE TABLE [TestPartitionTable]
WITH(Partitions(1)) -- partition number
其他參考資料
https://dotblogs.com.tw/stanley14/2017/12/10/SQL_Truncate_by_PartitionNumber
SSMS設定
