How To Partition In SQL Server | Tutorial | SQL Partition By Date

Here we are going to look at partitioning tables in SQL Server. In this tutorial we partition by date.

Advantages of Partitioning Tables in SQL Server

There are multiple benefits to partitioning. Some of these may include faster queries for example. By having a big table divided up into smaller chunks, you reduce the amount of data that needs to be scanned and accessed.

Think of it as having to find all records for 2016. Now if you had all your data partitioned by year then SQL Server would know to just read the 2016 partition, reducing your reads greatly.

Partition Switching (Swapping)

Archiving or purging data can also be greatly improved, in this tutorial we look at partition switching (or partition swapping as some people refer to it as). Here we can switch a partition into a staging table, so in the above example we can switch all 2016 data into a replica table, archive this off elsewhere. Its a fantastic feature

Truncate Partitions

Using the TRUNCATE TABLE tablename WITH (PARTITIONS) command we are able to now truncate specific partitions without affecting the remaining data. This is awesome! Imagine your data cleansing routines at the end of the year just getting rid of data from 5 years ago in a second! Makes admin tasks a lot easier.

SSIS Imports (ETL)

Consider any ETL or SSIS packages which need to import large amounts of data, we can import this into a staging table, switch the partition and its done in seconds. All the heavy work is done into another table with no effects to main table.

Content

00:00 Partition by date intro
01:02 Adding Filegroups
02:27 Create Function and Scheme
03:58 Create staging tables
04:54 Partition Tables
07:50 Index considerations
08:24 Partition Switching
10:17 Truncate partition

[fcfc]

How do you decide the filesize, e.g. line 17?

[FungXianHu]

hello, thank you for sharing 😁. I do have questions though.
Is that means we have to create a table for each year (staging.salesorderdetail)?
if it is true, can we select data from more than a year (example: dec 2022 until 2023) without manually union 2 staging tables?