Reputation: 1545
I created a partitioned table in SQL server as follows:
--Add File Groups
Alter Database Test Add Filegroup [Part_20181201]
Go
Alter Database Test Add Filegroup [Part_20181202]
Go
--create files
Alter Database [Test] Add FILE ( NAME = N'Part_20181201', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Part_20181201.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_20181201]
Alter Database [Test] Add FILE ( NAME = N'Part_20181202', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Part_20181202.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_20181202]
--create partition function
CREATE PARTITION FUNCTION DailyFunction (datetime2) AS RANGE RIGHT FOR VALUES ('20181201','20181202');
--create schema
Create Partition Scheme Schemedaily as Partition DailyFunction
To (Part_Before20181201, [Part_20181201], [Part_20181202])
--Create TABLE
Create TABLE [TempTable] (
id int identity(0, 1) not null,
date datetime2 not null,
text char(8000)
)
On Schemedaily (date) ;
If we want to truncate some partitions:
TRUNCATE TABLE [dbo].[TempTable]
WITH (PARTITIONS (1 TO 2));
Now, is it possible to truncate a table partition by partition name?
I mean something like this:
TRUNCATE TABLE [dbo].[TempTable] WITH (PARTITIONS (Part_20181201));
Upvotes: 2
Views: 13938
Reputation: 2507
If you have Partition function for the table partition, you have to use the partition function to truncate particular partitions in the table
TRUNCATE TABLE [dbo].[TableName] WITH (PARTITIONS($PARTITION.DailyFunction('Part_201812017')));
Upvotes: 2
Reputation: 13460
First, these are not partition names, but file group names. And the documentation is pretty clear about that:
<partition_number_expression>
can be specified in the following ways:• Provide the number of a partition, for example:
WITH (PARTITIONS (2))
• Provide the partition numbers for several individual partitions separated by commas, for example:
WITH (PARTITIONS (1, 5))
• Provide both ranges and individual partitions, for example:
WITH (PARTITIONS (2, 4, 6 TO 8))
•
<range>
can be specified as partition numbers separated by the word TO, for example:WITH (PARTITIONS (6 TO 8))
You can find the partition numbers in file group using query like this:
SELECT p.partition_number
FROM sys.partitions p
INNER JOIN sys.indexes i ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
INNER JOIN sys.filegroups AS fg ON fg.data_space_id = i.data_space_id
WHERE p.object_id = object_id('dbo.TempTable')
and fg.name = 'Part_20181201'
and p.index_id = 1 /* clustered index or 0 for heaps */
And then to construct a dynamic sql to truncate your table.
Here is a query to check on which file group is each partition number located for each object and index in your database:
SELECT object_name(p.object_id) as ObjectName, p.index_id, p.partition_number, fg.name
FROM sys.partitions p
INNER JOIN sys.indexes i ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
INNER JOIN sys.filegroups AS fg ON fg.data_space_id = i.data_space_id
order by ObjectName, p.index_id, p.partition_number, fg.name
Upvotes: 1
Reputation: 56
By Microsoft Definition Of WITH PARTITIONS, No You Can't
can be specified in the following ways:
• Provide the number of a partition, for example:
WITH (PARTITIONS (2))
• Provide the partition numbers for several individual partitions separated by commas, for example:
WITH (PARTITIONS (1, 5))
• Provide both ranges and individual partitions, for example:
WITH (PARTITIONS (2, 4, 6 TO 8))
•
<range>
can be specified as partition numbers separated by the word TO, for example:WITH (PARTITIONS (6 TO 8))
To truncate a partitioned table, the table and indexes must be aligned (partitioned on the same partition function).
See The Link Blow https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-2017
Upvotes: 2