4

I would like to know how to drop partition by name in SQL SERVER like MySQL do it, example :

CREATE TABLE t1 (
        id INT,
        year_col INT
    )
    PARTITION BY RANGE (year_col) (
        PARTITION p0 VALUES LESS THAN (1991),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (1999)
    );
ALTER TABLE t1 DROP PARTITION p0, p1;

A new partition is created everyday and dropped old ones. Because, it's a daily partition.

I've setup a partition like:

    CREATE PARTITION FUNCTION [pf_trans_date](datetime2(3)) AS RANGE LEFT 
FOR VALUES (N'2019-03-27T00:00:00.000', N'2019-03-28T00:00:00.000')


 CREATE PARTITION SCHEME [sh_trans_date] AS PARTITION [pf_trans_date] 
TO ([PRIMARY], [PRIMARY])

and i can add a new partition :

ALTER PARTITION SCHEME [sh_trans_date]
 NEXT USED [PRIMARY]
 ALTER PARTITION FUNCTION [pf_trans_date_byhour]() SPLIT RANGE('2019/03/29')

Now the problem is how can i drop the old partition date 2019-03-27T00:00:00.000 and the all the data in it. Coz, i don't see any function that does it in SQL Server 2017.

jarlh
  • 42,561
  • 8
  • 45
  • 63
kizawa tomaru
  • 103
  • 1
  • 8
  • 1
    Take a look a this video: https://www.youtube.com/watch?v=aoLGMFwpdyg They `switch partition` to other table – vercelli Mar 27 '19 at 13:02
  • Ok, i go watch it. – kizawa tomaru Mar 27 '19 at 13:06
  • 1
    @vercelli, thank you very much for the response. However, I don't understand why they do it so complicates. I'll try to assimilate it in my little brain. :P – kizawa tomaru Mar 27 '19 at 13:25
  • 1
    @kizawatomaru - switching the partition to an empty table allows for archiving type patterns where instead of merely dropping old data you join it into a different table that keeps old data around. If you don't want that (and if you're on SQL 2016+), `truncate table` takes a partition argument. Either way you do it, you should probably remove the old partition boundary. – Ben Thul Mar 27 '19 at 13:47
  • @BenThul - you were right. `truncate table` is not enough, I have to remove the old partition boundary too. But the way to do it is not a for a kid as i am. – kizawa tomaru Mar 27 '19 at 14:00

0 Answers0