1

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.

enter image description here

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

enter image description here

  • 1
    I've found this method for deleting data very efficient: michaeljswart.com/2014/09/take-care-when-scripting-batches – Erik Darling Apr 21 '17 at 12:15
  • yes, that is the way I normally do them big deletes, however, I wanted to see if I could take a bit more advantage on the partitioning, either for faster I/O or lock management.In this other comment of yours is very helpful, but I am current on sql2014 not sql2016 – marcello miorelli Apr 21 '17 at 12:21
  • 1
    Why don't you want to swap out the partition in question and then truncate it after the swap? You may even be able to adjust the query in this DBA.SE question to your needs. – John Eisbrener Apr 21 '17 at 14:47
8

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.

  • An interesting follow-up question might be: would it make sense to split a partition if you need to delete a subset of the partition, and then merge back the partition? – John Zabroski Nov 23 '18 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.