|
----建库、建组,首先建立我们要使用的数据库，最重要的是建立多个文件组
create? database? Sales on primary
(
?? name=N'Sales',
?? filename=N'D:dataPrimarySales.mdf',
?? size=3MB,
?? maxsize=100MB,
?? filegrowth=10%
),
filegroup FG1
(
? NAME = N'File1',??
? FILENAME = N'D:dataFG1File1.ndf',??
? SIZE = 1MB,??
? MAXSIZE = 100MB,??
? FILEGROWTH = 10%
),
FILEGROUP FG2??
(??
? NAME = N'File2',??
? FILENAME = N'D:dataFG2File2.ndf',
? FILEGROWTH = 10%??
),
FILEGROUP FG3??
(??
? NAME = N'File3',??
? FILENAME = N'D:dataFG3File3.ndf',??
? FILEGROWTH = 10%??
)??
LOG ON??
(??
? NAME = N'Sales_Log',??
? FILENAME = N'D:dataPrimarySales_Log.ldf',??
? FILEGROWTH = 10%
)
GO
-----二：建立分区函数，目的是用来规范不同数据存放到不同目录的标准，简单讲就是如何分区。
USE Sales??
GO
CREATE PARTITION FUNCTION pf_OrderDate (datetime)??
AS RANGE RIGHT??
FOR VALUES ('2003/01/01','2003/01/10')
GO
/***
我们创建了一个用于数据类型为datetime的分区函数，按照时间段来划分。
文件组 分区 取值范围
FG1 1 (过去某年,2003/01/01)
FG2 2 [2003/01/01,2004/01/01)
FG3 3 [2004/01/01,未来某年)
***/
-------三：创建分区方案，关联到分区函数。目的就是我们将已经建立好的分区函数组织成一套方案，简单点将就是我们在哪里对数据进行分区。
Use Sales
go
create? partition? scheme ps_OrderDate
as partition? pf_OrderDate
to(FG2,FG2,FG3)
go
------四：创建分区表。创建表并将其绑定到分区方案上。我们首先建立两个表，一张原始表另一张用来归档数据，保存归档数据。
Use Sales
go
create table Orders
(
?? OrderID int identity(10000,1),
?? OrderDate datetime? not null,
?? CustomerID int not null,
?? constraint? PK_Orders primary key(OrderID,OrderDate)
)
on ps_OrderDate(OrderDate)
go
create table OrdersHistory
(
?? OrderID int identity(10000,
?? constraint? PK_OrdersHistory primary key(OrderID,OrderDate)
)
on ps_OrderDate(OrderDate)
go
------五：测试，插入记录USE Sales??
USE Sales??
GO??
INSERT INTO dbo.Orders (OrderDate,CustomerID) VALUES ('2003/01/01',1000)??
INSERT INTO dbo.Orders (OrderDate,CustomerID) VALUES ('2003/01/13',CustomerID) VALUES ('2003/01/05',CustomerID) VALUES ('2003/01/23',1000)
INSERT INTO dbo.Orders (OrderDate,CustomerID) VALUES ('2003/05/23',1000)
GO
------六：验证数据完整行
select * from Sales.dbo.Orders;
select * from Sales.dbo.OrdersHistory;
select * from dbo.Orders where $partition.pf_OrderDate(OrderDate)=1;
select * from dbo.Orders where $partition.pf_OrderDate(OrderDate)=2;
------七：按照这个分区进行分组来查看各个分区的数据行多少
select $partition.pf_OrderDate(OrderDate) as Patition,COUNT(*) countRow from dbo.Orders
group by $partition.pf_OrderDate(OrderDate)
----还可以通过$Partition函数获得一组分区标识列值的分区号，例如获得属于哪个分区
select $partition.pf_OrderDate('2002')
union all
select $partition.pf_OrderDate('2003')
union all
select $partition.pf_OrderDate('2004')
------八：添加分区
USE [master]
GO
ALTER DATABASE [Sales] ADD FILEGROUP [FG4]
GO
ALTER DATABASE [Sales] ADD FILE ( NAME = N'File4',FILENAME = N'G:dataFG4File4.ndf',SIZE = 3072KB,FILEGROWTH = 1024KB ) TO FILEGROUP [FG4]
GO
----九：查看元数据 select * from sys.partition_functions;?? select * from sys.partition_range_values; select * from sys.partition_schemes;
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
