CODEWITHSUNDEEP
sqlsql-serverdatabasepartitioning
Rezaeimh7
Rezaeimh7

Reputation: 1545

sql server Truncate table's partitions by partition name

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

Answers (3)

Vijai
Vijai

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

Andrey Nikolov
Andrey Nikolov

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

Iman Doostie
Iman Doostie

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

Related Questions