2

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

3
  • 1
    I've found this method for deleting data very efficient: michaeljswart.com/2014/09/take-care-when-scripting-batches Apr 21, 2017 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 Apr 21, 2017 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. Apr 21, 2017 at 14:47

1 Answer 1

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.

1
  • 1
    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? Nov 23, 2018 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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