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