Freigeben über

SQLSweet16!, Episode 5: TRUNCATE Selected Partitions

  • Artikel

Reviewed By: Denzil Ribeiro, Kun Cheng

Deleting all rows from a given partition is a very common operation on a partitioned table, especially in a sliding window scenario. In a sliding window scenario, when a new period starts, a new partition is created for the new data corresponding to this period, and the oldest partition is either removed or archived.

To remove or archive the oldest partition, the general practice is to switch the partition out to a temporary staging table. The SWITCH operation for a partition is a simple statement, but it takes a bit of preparation for the SWITCH to work. The staging table needs to follow certain rules:

  • the staging table must have the same structure as the main partitioned table
  • the staging table must be empty
  • must reside on the same file group as partition being switched out
  • must create all matching clustered and non-clustered indexes

If the data from the oldest partition need to be archived and saved somewhere, it makes sense to switch the data out to a staging table and process for archiving. However, if the goal is simply to delete the data from the partition, then the programming needed for creating the staging table and switching partition may be cumbersome.

SQL Server 2016 addresses this by allowing TRUNCATE operation on individual partitions of a table. SQL Server 2016 introduces a WITH PARTITIONS clause for TRUNCATE TABLE statement that allows specifying a selected set of partitions (you can specify more than one partition at once). Needless to say that like truncating a table, truncating a partition is also a meta-data operation.

Example:

 TRUNCATE TABLE DB1BTicket WITH (PARTITIONS (7, 8))
GO

Comments

  • Anonymous
    August 13, 2016
    Awesome !!! easy partition out