Partition in Sql Server 

 

When the table expands in size and over time, due to 

• Increasing number of inserts.
• Or Increase number of updates or deletions

The extent of the table increases. 

OR

If this table is allowed to accumulate millions of rows over time, not in the billions, and the database's size can reach 100 gigabytes, 200 gigabytes, or even more in certain locations in terabytes

In above situations, we will need to consider a different approach. And this is where the concept of data partitioning or table partitioning is introduced.

 

 

When Partition required

As the table gets bigger and bigger in time, it becomes more difficult to manage the space required. The speed of data retrieval, the increased i o activity, the hardware resources, the deletes, the inserts, the updates, the selects and even the backup and restores all start pressing  upon the SQL Server and the hardware.
 

To remedy this, one can use what we consider what's called table partitioning and primarily table partitioning.  Simply put, divides the content of a table into smaller, manageable chunks of data in a what's called a file group.

 

 

What is partitioning?

The data in partitioned tables and indexes is split up into units that can be stored in different file groups in a database. The data is split up so that each group of rows goes into its own partition. Only the enterprise edition has table partitioning. 

 

 

Partition by years

Create partition for each year and dump the data into each file group/container 

 

 

What are primary files / file-groups

A filegroup is a logical storage unit that holds database objects that are maps to a file system or more than one file. 

 

 

Steps required in Table Partition

• Create file groups in individual disk drives
• Create files in each file groups
• Create partition functions 
• Create a partition scheme
• Partition the table based on the respective date time column