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
Auf dieser Seite können Sie das Online-Video How To Partition In SQL Server | Tutorial | SQL Partition By Date mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer The SQL Guy 16 August 2023 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 4,842 Mal angesehen und es wurde von 30 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
