what could be the fastest way to delete all data from a partition of a partitioned table?

Question

I have a partitioned table that in real life has 80 million rows.

for testing purposes I have created and partitioned this table here.

when I run the following query:

select * from countries
where visit >= '20110101'
  and visit <= '20111231'

as you can see in the query plan here and on the picture below, it uses partition elimination so I know I am doing something right.

I am aware that partition generally is not meant to speed up my queries, it is a management feature, however, it can speed up queries on large tables.

I will start by stating what I don't want. I don't want to remove any partition from my table.

what I want?

I want to delete all data from a partition in the quickest possible way:

can something be quicker than this? without considering deleting in batches

BEGIN TRANSACTION T1

DELETE 
FROM dbo.countries WITH (TABLOCKX)
WHERE visit >= '20110101'
  AND visit <= '20111231'

--COMMIT TRANSACTION T1

query plan is here

Answer 1

SQL Server 2014 unfortunately doesn't support TRUNCATE on a partition. Either drop and recreate it or switch it out.

See longer discussion here.

SQL Server 2016 does support truncating partitions. If you're on that version, that's definitely your fastest option.