Partitioning in SQL Server

The data is partitioned horizontally in that the different groups of rows are put in different partitions.
ProTip: Table/Index Partitions can be placed on:

All partitioning benefits apply regardless of the number of filegroups used.
ProTip: When using tiered storage, using multiple filegroups lets you assign specific partitions to specific storage tiers. For example, you can store older, less frequently accessed data on less expensive storage.
ProTip: When storing all partitions on the same filegroup, it does enable us to automate sliding windows.
Key Concept Behind Partition: Partitioning is a performance optimization for large tables and indexes that splits the object horizontally into smaller units. When the tables or indexes are subsequently accessed, SQL Server can perform an optimization called partition elimination, which allows only the required partitions to be read, as opposed to the entire table.
Partitioning Concepts include:

Define Partition Function
Define Partition Scheme
For new table, create it on partition scheme.
For existing table, drop and re-create table's partition index.

Partition Key is used to determine in which partition each row of the table should be placed.
Important: The column you select as Partition Key, should also be a column that queries will use as a filter criterion. This will allow you to achieve partition elimination.
Important: The partitioning key must be a subset of the following indexes, that your table has:

Clustered Index
Primary Key (if it is different from the Clustered Index) : must if you want to use the SWITCH ability of Partition feature. Otherwise you get an error that reads, "Partition columns for a unique index must be a subset of the index key".
Unique Key

Columns of the following data-type (that are commonly used) cannot be a Partition Key:

Note: If a Computed Column is persisted, it is eligible to be a Partition Key.
Usually we will use a date or datetime column as the partitioning key to implement sliding windows based on time.

Partition Function is a database object where the upper and lower limits of each partition is configured.
In Partition Function configuration, you usually have a list of boundary points and specify the direction as LEFT/RIGHT, which determines how the boundary values are computed.
The partition function also dictates the data type of the partitioning key.

Partition Scheme is a database object where you configure if the partitions are all stored in same or multiple filegroups.
You can specify which filegroup each partition is stored on. To store all partitions on the same filegroup, you use the ALL keyword.
When you create a partition scheme, however, it is possible to specify an extra filegroup. This will define the next filegroup that should be used if an additional boundary point is added.
Objects involved in partitioning, work in a 1-to-many hierarchy like below:
As a use case, all tables whose rows are to be rolled over say monthly, can be partitioned by MonthlyPartitionScheme that uses a MonthlyPartitionFunction, for instance.

An index is considered aligned with the table if it is built on the same partition function as the table.
It is also considered aligned if it is built on a different partition function, but the two functions are identical, in that they share the same data type, the same number of partitions, and the same boundary point values.
Note: A clustered index is always aligned with the table.
A non-clustered can be independently partitioned and thus be non-aligned to the base table.
Aligning indexes with the base table is a good practice because aligning indexes can assist with partition elimination.
Important: For SWITCH operation of Partition, Index Alignment is required.

Blog @ Codonomics