Data warehouses and data repositories often have tables that store millions of records and each day adding thousands more.  As the data ages, the older records generally require fewer updates.  If all of these records are kept in a single physical table.  Queries and maintenance on the table and indexes will take longer to execute.  One alternative to keeping all of the data together is to split the table into smaller units.  SQL Server allows you to partition tables into smaller files and filegroups to make management and access more efficient for your large datasets.  For instance if you are collecting 1 million records daily.  You could create a partition for each month based on the transaction date by creating 12 filegroups to store the monthly partitions.  In this scenario, you would have a single logical entity that represents the entire table.  However, it is physically divided into 12 files in the database.  The applications that perform CRUD operations continue to operate normally and do not require code changes.  However, on the database you would need to configure the table to be a partitioned table.  SQL Server 2014 support up to 15,000 partitions of a table.

Table Partitions can have the following benefits.

In order to create a table partition you need to create a partition function, partition schema, and appropriate files to store the partitions.

These are the steps and scripts required to create a partition on the Sales.SalesOrderHeader table in the AdventureWorks database.

First create a partition function to sort the data based on a datetime value.  In this example, I am going to use the OrderDate on table Sales.SalesOrderHeader to determine which partition to place the data in.