SQL Server 2008读书笔记（6）：数据分布和分区

Lesson 1: Creating a Partition Function

CREATE PARTITION FUNCTION
mypartfunction (int)
AS RANGE LEFT
FOR VALUES (10,20,30,40,50,60)

Quick Check
1. What data types cannot be used with partition functions?
2. What is the maximum number of partitions allowed for a table?
3. What is the maximum number of boundary points allowed for a partition function?

Quick Check Answers
1. You cannot use text, ntext, image, xml, varbinary(max), varchar(max), or any CLR
2. The maximum number of partitions for a table is 1,000.
3. The maximum number of boundary points for a partition function is 999.

--Create a database with multiple filegroups.
USE master
GO

CREATE DATABASE partitiontest
ON PRIMARY (NAME = primary_data, FILENAME = 'c:\test\db.mdf', SIZE = 2MB),
FILEGROUP FG1 (NAME = FG1_data, FILENAME = 'c:\test\FG1.ndf', SIZE = 2MB),
FILEGROUP FG2 (NAME = FG2_data, FILENAME = 'c:\test\FG2.ndf', SIZE = 2MB),
FILEGROUP FG3 (NAME = FG3_data, FILENAME = 'c:\test\FG3.ndf', SIZE = 2MB),
FILEGROUP FG4 (NAME = FG4_data, FILENAME = 'c:\test\FG4.ndf', SIZE = 2MB),
FILEGROUP FG5 (NAME = FG5_data, FILENAME = 'c:\test\FG5.ndf', SIZE = 2MB),
FILEGROUP FG6 (NAME = FG6_data, FILENAME = 'c:\test\FG6.ndf', SIZE = 2MB),
FILEGROUP FG7 (NAME = FG7_data, FILENAME = 'c:\test\FG7.ndf', SIZE = 2MB),
FILEGROUP FG8 (NAME = FG8_data, FILENAME = 'c:\test\FG8.ndf', SIZE = 2MB),
FILEGROUP FG9 (NAME = FG9_data, FILENAME = 'c:\test\FG9.ndf', SIZE = 2MB),
FILEGROUP FG10 (NAME = FG10_data, FILENAME = 'c:\test\FG10.ndf', SIZE = 2MB),
FILEGROUP FG11 (NAME = FG11_data, FILENAME = 'c:\test\FG11.ndf', SIZE = 2MB),
FILEGROUP FG12 (NAME = FG12_data, FILENAME = 'c:\test\FG12.ndf', SIZE = 2MB),
FILEGROUP FG13 (NAME = FG13_data, FILENAME = 'c:\test\FG13.ndf', SIZE = 2MB)
LOG ON (NAME = db_log, FILENAME = 'c:\test\log.ndf', SIZE = 2MB, FILEGROWTH = 10% );
GO

USE partitiontest
GO

--Create a partition function with boundary points for each month
CREATE PARTITION FUNCTION partfunc (datetime) AS
RANGE RIGHT FOR VALUES ('1/1/2005','2/1/2005','3/1/2005','4/1/2005','5/1/2005',
'6/1/2005','7/1/2005','8/1/2005','9/1/2005','10/1/2005','11/1/2005',
'12/1/2005')
GO

SELECT * FROM sys.partition_range_values;

Lesson Summary

• A partition function defi nes the boundary points for a set of partitions.
• You can create a partition function as either RANGE LEFT or RANGE RIGHT.
• You can utilize any data type except: text, ntext, image, varbinary(max), varchar(max),XML, or CLR data types.

Lesson 2: Creating a Partition Scheme

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )

Three examples of partition schemes are as follows:

CREATE PARTITION SCHEME mypartscheme AS PARTITION mypartfunction TO (Filegroup1,
Filegroup2, Filegroup3, Filegroup4, Filegroup5, Filegroup6, Filegroup7)

CREATE PARTITION SCHEME mypartscheme AS PARTITION mypartfunction TO (Filegroup1,
Filegroup1, Filegroup2, Filegroup2, Filegroup3)

CREATE PARTITION SCHEME mypartscheme AS PARTITION mypartfunction ALL TO (Filegroup1)

 

CREATE PARTITION FUNCTION
mypartfunction (int)
AS RANGE LEFT
FOR VALUES (10,20,30,40,50,60);
GO

CREATE PARTITION SCHEME mypartscheme AS PARTITION mypartfunction TO (Filegroup1,
Filegroup2, Filegroup2, Filegroup4, Filegroup5, Filegroup6, Filegroup7);
GO

Quick Check
1. How many fi legroups can you specify if you use the ALL keyword when defining a partition scheme?
2. Can you create a new fi legroup at the same time that you are creating a partition scheme?

Quick Check Answers
1. You can specify exactly one fi legroup when using the ALL keyword.
2. No. Any filegroups that you specify in the CREATE PARTITION SCHEME statement must already exist in the database.

CREATE PARTITION SCHEME partscheme AS
PARTITION partfunc TO
([FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10], [FG11],
[FG12], [FG13])
GO

--View the partition scheme
SELECT * FROM sys.partition_schemes;

Lesson Summary

• A partition scheme is a storage defi nition containing a collection of fi legroups.
• If you specify the ALL keyword, the partition scheme allows only a single fi legroup to be specifi ed.
• If you do not specify the ALL keyword, you must specify enough fi legroups to map all the partitions created by the partition function.

Lesson 3: Creating Partitioned Tables and Indexes

CREATE TABLE Employee (EmployeeID int NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL)
ON mypartscheme(EmployeeID);
GO

CREATE NONCLUSTERED INDEX idx_employeefirtname
ON dbo.Employee(FirstName) ON mypartscheme(EmployeeID);
GO

Quick Check

1. What property must be set to use a computed column as a partitioning key?
2. Which clause of the CREATE TABLE or CREATE INDEX statements is used to partition the object?

Quick Check Answers
1. A computed column must be PERSISTED.
2. The ON clause is used to specify the storage structure, fi legroup, or partition scheme, for the table or index.

CREATE TABLE dbo.orders (
    OrderID int identity(1,1),
    OrderDate datetime NOT NULL,
    OrderAmount money NOT NULL
    CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (OrderDate,OrderID))
ON partscheme(OrderDate)
GO

 

SET NOCOUNT ON
DECLARE @month int,
        @day int
SET @month = 1
SET @day = 1
WHILE @month <= 12
    BEGIN
        WHILE @day <= 28
        BEGIN
            INSERT dbo.orders (OrderDate, OrderAmount)
            SELECT cast(@month as varchar(2)) + '/' + cast(@day as varchar(2))+ '/2005', @day * 20
            SET @day = @day + 1
        END
        SET @day = 1
        SET @month = @month + 1
    END
GO

 

SELECT * FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.orders')

Lesson Summary

1. The ON clause is used to specify the storage structure, fi legroup, or partition scheme to store a table or index.
2. The partitioning key must match the data type, length, and precision of the partition function.
3. A computed column used as a partitioning key must be persisted.

Lesson 4: Managing Partitions

ALTER PARTITION FUNCTION partition_function_name()
{SPLIT RANGE ( boundary_value )
| MERGE RANGE ( boundary_value ) } [ ; ]

 

ALTER PARTITION SCHEME partition_scheme_name
NEXT USED [ filegroup_name ] [ ; ]

 

Quick Check
1. Which operators are used to add or remove boundary points from a partition function?
2. Which operator is used to move partitions between tables?

Quick Check Answers
1. The SPLIT operator is used to introduce a new boundary point. The MERGE operator is used to remove a boundary point.
2. The SWITCH operator is used to move partitions between tables.

Practice:

ALTER PARTITION SCHEME partscheme
NEXT USED [FG1];
GO

ALTER PARTITION FUNCTION partfunc()
SPLIT RANGE ('1/1/2006');
GO

CREATE TABLE dbo.ordersarchive (
    OrderID int NOT NULL,
    OrderDate datetime NOT NULL CONSTRAINT ck_orderdate CHECK (OrderDate<'2/1/2005'),
    OrderAmount money NOT NULL
    CONSTRAINT pk_ordersarchive PRIMARY KEY CLUSTERED (OrderDate,OrderID)
)
ON FG2
GO

ALTER TABLE dbo.orders
SWITCH PARTITION 2 TO dbo.ordersarchive
GO

ALTER PARTITION FUNCTION partfunc()
MERGE RANGE ('1/1/2005');
GO

Lesson Summary

• SPLIT is used to introduce a new boundary point to a partition function.
• MERGE is used to remove a boundary point from a partition function.
• SWITCH is used to move partitions between tables.