Truncate partitioned table with values from partitioned column?

Question

I have partitioned a table on a column with varchar datatype.

The documentation on truncate table with partitioning uses the partitionids:

Truncate table parttable with (partitions (1,2,5))

I only have a list of the actual values from the partitioned column.

How can I get the partitionids from a list of values so I can use it in the truncate table statement?

Background:

I’m still very new to partitioning and need to validate my plan.

I have a fact table that is partitioned on a column with varchar datatype.

I have an etl process that loads data fully in the beginning of the month.

The data loads after that only contain a subset (the partitioned column) and needs to fully replace the existing data in the fact table.

My plan is:

1. Load the data first to a staging table.

2. Truncate the fact table on the partitioned values in the staging table.

3. Insert the data in the staging table to the fact table.

Am I doing this correctly?

Answer 1

Like this:

create partition function pf(int) as range right for values (1,2,3,4,5)
create partition scheme ps as partition pf all to ([Primary])
create table parttable(id int primary key, a int, b int, c int) on ps(id)
insert into parttable(id,a,b,c) values (0,0,0,0), (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6)
Truncate table parttable with (partitions ($partition.pf(1),$partition.pf(2),$partition.pf(5)))
select * from parttable

outputs

id a b c
----------- ----------- ----------- -----------
0 0 0 0
3 3 3 3
4 4 4 4

And you can do this dynamically like this:

declare @listOfPartitionColumnValues nvarchar(max) = '1,2,5'
declare @listOfPartitionNumbers nvarchar(max) = 
 ( 
 select string_agg(pn,',')
 from
 (
 select distinct $partition.pf(v.value) pn
 from string_split(@listOfPartitionColumnValues,',') v
 ) p
 )
declare @sql nvarchar(max) = concat('truncate table parttable with (partitions (',@listOfPartitionNumbers ,'))')
print @sql
exec (@sql)

Answer 2

Since you didn't provide a minimal, complete, and verifiable example, I've created one:

DROP TABLE IF EXISTS dbo.pt;
IF EXISTS (SELECT 1
 FROM sys.partition_schemes ps
 WHERE ps.name = N'ps'
 )
BEGIN
 DROP PARTITION SCHEME ps;
END
IF EXISTS (SELECT 1
 FROM sys.partition_functions pf
 WHERE pf.name = N'p'
 )
BEGIN
 DROP PARTITION FUNCTION p;
END
CREATE PARTITION FUNCTION p
(
 int
)
AS RANGE RIGHT
FOR VALUES (10, 20, 30);
CREATE PARTITION SCHEME ps
AS PARTITION p ALL TO ([DEFAULT]);
CREATE TABLE dbo.pt
(
 i int NOT NULL
 CONSTRAINT pt_pk
 PRIMARY KEY
 CLUSTERED
) ON ps(i);
INSERT INTO dbo.pt (i)
SELECT TOP(30) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc;

That will create a partition scheme, a partition function, and a partitioned table with 30 rows inserted across the three paritions.

The following query determines which values lie on what partitions:

;WITH rowsource AS
(
SELECT pt.i
 , plc.file_id
 , plc.page_id
 , plc.slot_id
FROM dbo.pt
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc
WHERE pt.i = 1
 OR pt.i = 22
)
SELECT rs.*
 , dpa.partition_id
FROM rowsource rs
OUTER APPLY sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.pt', N'U'), NULL, NULL, 'DETAILED') dpa
WHERE dpa.allocated_page_file_id = rs.file_id
 AND dpa.allocated_page_page_id = rs.[page_id]
ORDER BY rs.i
 , dpa.partition_id;

Note in the Common Table Expression (CTE), there is a WHERE clause limiting the output to the values (1) and (12) in the i column of the dbo.pt table.

The output looks like:

╔════╦═════════╦═════════╦═════════╦══════════════╗
║ i ║ file_id ║ page_id ║ slot_id ║ partition_id ║
╠════╬═════════╬═════════╬═════════╬══════════════╣
║ 1 ║ 1 ║ 40 ║ 0 ║ 1 ║
║ 22 ║ 1 ║ 328 ║ 2 ║ 3 ║
╚════╩═════════╩═════════╩═════════╩══════════════╝

As you can see, those values lie on partitions 1 and 3 respectively.

You could extend the query above by joining to a #temp table with the list of values you need the partition_id values for.

The following code will automatically truncate the partition for the row containing the value 14:

DECLARE @cmd nvarchar(max);
DECLARE @partitions nvarchar(max);
SET @partitions = N'';
;WITH rowsource AS
(
SELECT pt.i
 , plc.file_id
 , plc.page_id
 , plc.slot_id
FROM dbo.pt
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) plc
WHERE pt.i = 14
)
SELECT @partitions = STUFF(q.p, 1, 2, N'')
FROM (
SELECT ', ' + CONVERT(nvarchar(max), dpa.partition_id)
FROM rowsource rs
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.pt', N'U'), NULL, NULL, 'DETAILED') dpa
WHERE dpa.allocated_page_file_id = rs.file_id
 AND dpa.allocated_page_page_id = rs.[page_id]
GROUP BY dpa.partition_id
ORDER BY dpa.partition_id
FOR XML PATH(N'')
) q(p);
SET @cmd = N'TRUNCATE TABLE dbo.pt WITH (PARTITIONS (' + @partitions + N'));';
PRINT @cmd;
--EXEC sys.sp_executesql @cmd; --uncomment this line to actually truncation the partitions