Delete all the Data of a Partition

If you want to delete blocks of data, you usually would like to delete blocks instead of deleting rows. If you at the same time use partitions to store your data, and all the data that you want to delete (e.g. all the data from 2010 and before), you can use the partition commands to realize this. Instead of deleting row by row with the delete-Command
delete * from table where timestamp < '2011-01-01'

you would then truncate the data of the partition(s) that contains this data (from 2010 and before). To do this, you have to create a staging table and then use the SWITCH TO command to move the data to this table. Both steps could be done using the SQL Server wizards. If you have created partitions on a table, you could right-click on the table name, choose "Storage", then "Manage partition":



In the wizard, you choose "Manage partitioned data in a sliding window scenario" and then enable "Switch Out":


The wizard composes now SQL commands to create a staging table with the same columns and indexes as the original table. However, to delete a partition's data you wouldn't need to create all the indexes, it's enough to create the columns. Then you have to invoce the SWITCH TO command to move the data to the staging table:

ALTER TABLE table SWITCH PARTITION 1 TO stagingTable

This is a meta data-operation, no data is moved physically, and thus the data movement doesn't cost a lot. To delete the data, you can then drop the table or truncate it. Both is a block operation and, thus, won't harm the database.

PS: In case, that other tables have foreign keys to this table, you would have to delete this data beforehand (since it might not make sense to have it without its references anyway). Most usually, you would have these referenced tables also partitioned so that you should think about deleting the partitions of referenced tables in the same transaction.