No tags yet.
This post is meant to be a quick primer on Table Partitioning in SQL Server.
What is it?
Let's say you have a large table that is accepting a lot of transactions, partitioning allows you to scale this particular table out into different (physical partitions). Thus spreading out the I/O across multiple files and even disks.
Why do I need it?
While the integrity of the data or table remains intact, almost transparent to front end queries (still seen as a single table), but the data is actually split into subsets. This may allow sql server to traverse only through the relevant subset thus making this process quicker.
You also have flexibility in that you can compress the data in the partition(s). Furthermore it can ease up pains with index maintenance in that you can rebuild partitions of the index instead of the whole thing.
This is not a guarantee that query performance will be improved. This is very dependent on the types of queries and the hardware configuration such as the storage/hard disks.
And of course you can setup an archival process. Let's say you have a table that you need to have only the last 3 years of data, anything older can be partitioned off to another Filegroup. (you can use partitioning switching seen at the bottom of the script below).
The script below goes through the process of creating a database, setting up multiple filegroups and then setting up the components of partitioning. (function, scheme, column). Please be sure to read the comments.
For a more in depth analysis of partitioning feel free to go to here.