Partitioning Tables #

Table partitioning is a technique of managing or improving performance in large databases by dividing larger tables into smaller, easier to manage pieces known as partitions. Each partition holds its own data, making it simpler for the system to manage the data within it.

The primary goal of partitioning is to aid in maintenance, improve performance and manageability, and to increase availability. It does so by splitting data across multiple physical pieces. This is exceptionally useful when dealing with massive tables and indexes.

In general, partitioning can be divided into three types: range partitioning, list partitioning, and hash partitioning.

  1. Range Partitioning: This type of partitioning is based on a range of values. This allows the division of data in a table into ranges which lead to a specific partition for storage.

  2. List Partitioning: List partitioning allows you to organize rows in a table according to distinct values of a specific column.

  3. Hash Partitioning: Hash partitioning employs a hash function on a certain key that disperses rows across partitions. This is ideal for equal distribution of data among partitions.

Clustered Tables #

A clustered table is a kind of table for which the data rows are stored in order based on the table's clustered index. The order of the data rows is decided by the clustered index key. Each table can possess only one clustered index.

The clustered index determines the physical order of data in a table. Thus, the terms "clustered index" and "clustered table" are often used interchangeably. A table without a clustered index is known as a heap.

The primary advantages of clustered tables include faster read operations for queries that return a range of values. Additionally, they provide the ability to rearrange data physically to match the order of the index, leading to more efficient use of storage.

To summarize, while both partitioned and clustered tables can boost the performance of a database, they function in distinct ways. Partitioned tables help handle large datasets by breaking them down into manageable portions, while clustered tables store data in an order that's based on their clustered index, enhancing read operations.