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?

New contributor
baatchen is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 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 2 days ago
5

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
  • 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 2 days ago
  • @dan - you should add that as an answer, along with the details about ALTER TABLE ... SWITCH – Max Vernon 2 days ago
3

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)
  • Thanks for all the answers, my scenario is that i will get a list of strings dynamically as a parameter. How can I iterate throught this list of value throguht the partition function to get the partitionids to then use in the truncate table with partitions command? And is that the best way to do it? – baatchen yesterday
  • Se updated answer. – David Browne - Microsoft yesterday
  • Thanks David, elegant code 😄! – baatchen yesterday
  • @baatchen You can accept the answer you found most helpful by clicking the check mark. – Paul White 9 yesterday

Your Answer

baatchen is a new contributor. Be nice, and check out our Code of Conduct.

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.