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