Implement partitioning solutions

May include but is not limited to: partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers).

Partitioning means that you store table data in different file groups, instead of using only one file group. By default, SQL Server treats every table as if it were partitioned, but having only one partition.

Partitioning is implemented horizontally, so that you store collections of rows in different file groups, not columns. You should consider partitioning a table when it is excepted to contain lots of data, which is being used in different ways. MSDN comes up with the example of a sales table, with the current month used primarily for inserts, and the previous ones for selects. This is a good candidate for partitioning solutions, because of the different operations performed on the different parts of the table.

To create a partitioned table, you need to do the following:

  • Create a partition function
  • Create a partition scheme
  • Create the table using the scheme

Continue reading “Implement partitioning solutions”