创建分区方案
为Orders数据表创建分区方案
在 Script3 – RangePartitionedTable.sql脚本中，查看并执行如下代码，创建分区方案。注意阅读一下以绿色显示的注释:
CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
TO ([PRIMARY], [2003Q3], [2003Q4],
[2004Q1], [2004Q2])
GO
AS
PARTITION OrderDateRangePFN
TO ([PRIMARY], [2003Q3], [2003Q4],
[2004Q1], [2004Q2])
GO
创建分区数据表
创建分区数据表，加载数据并通过分区函数验证记录所存放的位置
1. 在 Script3 – RangePartitionedTable.sql脚本中，查看并执行如下脚本，创建分区数据表 - OrdersRange:
CREATE TABLE AdventureWorks2008Test.[dbo].[OrdersRange]
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20030701'
AND [OrderDate] < '20040701'),
[TotalDue] [money] NULL
)
ON OrderDatePScheme (OrderDate)
GO
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20030701'
AND [OrderDate] < '20040701'),
[TotalDue] [money] NULL
)
ON OrderDatePScheme (OrderDate)
GO
2. 点击Execute 执行下列代码，通过INSERT…SELECT 语句从之前创建的Orders 数据表中将记录加载到OrdersRange 数据表中：
INSERT INTO OrdersRange
SELECT o.[OrderID]
, o.[EmployeeID]
, o.[VendorID]
, o.[TaxAmt]
, o.[Freight]
, o.[SubTotal]
, o.[Status]
, o.[RevisionNumber]
, o.[ModifiedDate]
, o.[ShipMethodID]
, o.[ShipDate]
, o.[OrderDate]
, o.[TotalDue]
FROM dbo.Orders AS o
GO
SELECT o.[OrderID]
, o.[EmployeeID]
, o.[VendorID]
, o.[TaxAmt]
, o.[Freight]
, o.[SubTotal]
, o.[Status]
, o.[RevisionNumber]
, o.[ModifiedDate]
, o.[ShipMethodID]
, o.[ShipDate]
, o.[OrderDate]
, o.[TotalDue]
FROM dbo.Orders AS o
GO
为了能够查看到数据位于哪个分区中，我们可以使用$partition. function(value) 函数来进行查看。该函数将跟据分区函数来返回相应结果所在的分区编号
