PARTITION

PARTITION is a more general term referring to the way tables or indexes are divided into smaller, manageable pieces.

Key Points:

• Partitioned Table: A table that is split into partitions based on the defined partition function and scheme. Each partition is stored in a separate filegroup.
• Partitioned Index: An index on a partitioned table, which is also divided according to the partitioning scheme.

Example of Viewing Partitions:

To view information about partitions and how data is distributed, you can use the following queries:

View Partition Information:

SELECT 
    partition_ordinal_position,
    partition_id,
    filegroup_id,
    rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Orders');

View Partition Function:

SELECT 
    name, 
    boundary_value_on_left
FROM sys.partition_functions
WHERE name = 'OrderDatePF';

View Partition Scheme:

SELECT 
    name, 
    partition_function_id
FROM sys.partition_schemes
WHERE name = 'OrderDatePS';

Summary:

Refers to the division of a table or index into smaller segments. In SQL Server, partitioning involves creating partition functions and schemes to manage how data is stored and queried.