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