concept partition scheme in category sql

This is an excerpt from Manning's book SQL Server MVP Deep Dives. Login to get full access to this book.

In a nutshell, a SQL Server–partitioned table is a table that’s referred to as one table but is subdivided into multiple segments or partitions. In fact, technically all database tables in SQL Server 2005 and later are partitioned with one implicit partition. Such tables aren’t normally referred to as explicitly partitioned. They can’t have more than one partition without also having been created on a partition scheme with a valid partition function. Only when a table has this additional property does it become a truly partitioned table.

Before we examine these features in greater detail, look at the summary of the relationship of a partitioned table, the partition scheme, and the partition function in figure 1.

Figure 1. Dependency relations of the major table partitioning components

A partitioned table (a table that can have more than the single default partition) must be bound to a partition scheme. A given partition scheme could be used by more than one table. In turn, a partition scheme must be defined using exactly one partition function, though a given function might be used by more than one partition scheme.