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
Смотрите видео How To Partition In SQL Server | Tutorial | SQL Partition By Date онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь The SQL Guy 16 Август 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 4,84 раз и оно понравилось 3 людям.