SQL Server partitions - How to create and use Sliding Windows - Part 2

How to create a SQL Server partition on a table in order to implement a sliding windows.

This is the second part of a multipart demonstration of using SQL Server partitioning. In the first installment we created a new database and table (of invoice data and amount) and then a partition function and partition scheme that uses the InvoiceDate on each of the table entries as a partitioning key. This way data is segregated at the file level into monthly sections. The table is linked to the partitioning scheme when we create a clustered index and specify the scheme rather than simply the static filegroup to use. That way SQL can dynamically decide where to place the data based on its date.

We examine the data prior to the partitioning being applied and can see that all the data resides on a single partition. Then once we apply the partitioning scheme to the table by creating a clustered index we can see the data split across the 4 partitions that we have created.

In this video we now take a look at how to implement the sliding window part. This involves dropping the oldest partition using a meta data operation rather than need to run a delete statment against it. By using the Partitioning SWITCH keyword we can move a single month worths of data to a separate table and then run TRUNCATE against it to achieve this. Then we drop the oldest partition using MERGE and create a new partition for the current month with the SPLIT keyword. So there is some overhead in maintaining this setup but it cases where we have Big data it makes it worth while in order to drop data using META operations only.

[ashrr]

how do you know when someone is filming from the UK. They're wearing a jacket and a hat inside the house. I really appreciate this video, because this topic is, I guess not widely discussed, at least in a coherent manner. You've enabled me to finally understand how partitioning works, something I have never touched or understood for such a long time, as a SQL DBA. That's nuts, thanks a lot.

[MohammedSadakathAli]

Much appreciated 😊. Please create video on dynamic partition as well.

[blackisblack]

What about switching a partition to slower storage, does it implies a copy/delete mechanism?

[dbberater]

Hey Mark, you can truncate partitions with the following command:

TRUNCATE TABLE invoice WITH PARTITION x