CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012database-partitioning
itzik Paz
itzik Paz

Reputation: 433

Is there a way to drop a range value from a partition table (sql server 2012)

I have a PARTITION table "TableA". I created a partition on column name "RecorDate" (DATE type). There are records from the last 6 months that I am not interested to save them, and I want to drop them.

Is there a simple way to drop a range vale OR specific partition number (or even a all range, for example '01-01-2014' to '01-01-2015').

something like in ORACLE, TRUNCATE TABLE WITH(PARTITION(x,y to z)).

BTW I know the switch partition command, to move the range to another table and then exec the command 

TRUNCATE TABLE TableNameb ;
GO

10X

Upvotes: 3

Views: 4711

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

TRUNCATE WITH PARTITION will be available with SQL Server 2016

WITH ( PARTITIONS ( { | } [ , ...n ] ) )

Applies to: SQL Server (SQL Server 2016 Community Technology Preview 2 (CTP2) through current version).

Specifies the partitions to truncate or from which all rows are removed. If the table is not partitioned, the WITH PARTITIONS argument

will generate an error. If the WITH PARTITIONS clause is not provided, the entire table will be truncated.

<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))

Example: 

TRUNCATE TABLE dbo.PartitionedTable WITH (PARTITIONS (2, 4, 6 TO 8))

SQL Server before 2016

Today you can use this:

As you note, there is a way to do this today by moving the partition to a second table through ALTER TABLE SWITCH and then TRUNCATE the second table. If both tables are part of the same filegroup, this should actually be a low-risk operation as no data is moved, we just update the metadata.

Example TABLE SWITCH

Upvotes: 3

Petah
Petah

Reputation: 127

i would say delete * from table_name. delete all from table. you can use the where statement after delete to be more precise. to adjust the command yourself look here http://www.w3schools.com/sql/sql_between.asp

Upvotes: 0

Related Questions