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