Microsoft® SQL Server® 2012 Bible

1118

Part VIII: Performance Tuning and Optimization

3. Create the partition scheme that assigns partitions to fi legroups.


4. Create or modify a table or index by specifying the partition scheme.
   Partition functions and partition schemes work together to segment the data, as shown in
   Figure 49-3.

FIGURE 49-3

The partition scheme uses the partition function to place the data in separate fi legroups.

Part01

Boundaries

defined by

the Partition

Function

Partition

Locations

defined by

the Partition

Scheme

Part02

1/1/2002 1/1/2003 1/1/2004 1/1/2005

Create Table(...) On Partition Scheme

Part03 Part04 Part05

Create the Filegroups

A best practice when implementing a partitioning strategy is to create a fi legroup for each

partition. The partition scheme created later maps a partition to a specifi c fi legroup. To

start with your partition creation process, add four fi legroups to the AdventureWorks data-

base. The following script illustrates how to accomplish this:

USE master;

ALTER DATABASE AdventureWorks

ADD FILEGROUP AdventureWorks_SalesOrderDetail2005Partition

GO

ALTER DATABASE AdventureWorks

ADD FILE

(

NAME = 'AdventureWorks_SalesOrderDetail2005Partition',

FILENAME =

'C:\SQLData\AdventureWorks_SalesOrderDetail2005Partition.ndf',

SIZE = 10,

MAXSIZE = 120,

FILEGROWTH = 10

)

TO FILEGROUP AdventureWorks_SalesOrderDetail2005Partition

c49.indd 1118c49.indd 1118 7/31/2012 10:24:23 AM7/31/2012 10:24:23 AM

http://www.it-ebooks.info