2

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?

  • Is your staging table in the same database as the target? If so, it would be better to partition the staging table with the same scheme as the target table and use TRUNCATE and SWITCH for each partition instead of INSERT. – Dan Guzman Feb 15 at 12:32
4

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
| improve this answer | |
  • 2
    I strongly suggest using the documented $PARTITION function to identify the partition numbers (e.g. SELECT $PARTITION.p(pt.id) instead of the undocumented (%%PHYSLOC%%). That will simply the queries too. – Dan Guzman Feb 15 at 12:35
  • @dan - you should add that as an answer, along with the details about ALTER TABLE ... SWITCH – Max Vernon Feb 15 at 17:42
4

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)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.