Database Reference
In-Depth Information
Getting ready
In order to begin the planning and design of the data partitioning solution, make sure the
SQL Server instance is a Datacenter or Enterprise edition for the production environment.
For testing purposes, we can use the Developer edition, which is equivalent to the Enterprise
edition and cannot be used as a production platform. There are three types of partitioning:
hardware, horizontal, and vertical.
F Hardware partitioning designs the solution by taking advantage of available
hardware architecture.
F Horizontal partitioning designs the solution dividing a table into multiple partitioned
tables, horizontally, depending upon how data is analyzed, which is ideal for queries
that require data for a specific value (date) to reference the appropriate table.
F Vertical partitioning designs the solution dividing a table into multiple tables that
contain fewer columns. This feature is helpful for queries to scan less data with
increased performance, provided the partitions are not larger.
To start with the data partitioning solution, we need to create a partitioned table, which is
listed as follows:
F Create additional filegroups if the partition needs to spread over multiple
file groups
F Place the newly created filegroups across different physical disk partitions. In
theory the maximum number of partitions allowed is 1000
F Create a partition function that will determine how the data is partitioned
F Create a partition scheme that will assign partitions to filegroups
F Create a table with a non-clustered primary key using the partition scheme
For more information and detailed steps, refer to the recipe Designing
Scalable Shared database features and enhancements mentioned in
Chapter 3 , Managing the Core Database Engine .
F Create a clustered index for the table using the partition scheme and the
partition function
F Create the indexes and by default these objects will also use the same partitioning
scheme and partitioning column
F For better manageability, indexes can be created using different partitioning functions
or on non-partitioned filegroups
 
Search WWH ::




Custom Search