Sql-server – Truncate partitioned table with values from partitioned column

partitioningsql server

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?

Best Answer

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
Related Question