SQLSERVER数据分区

----建库、建组,首先建立我们要使用的数据库，最重要的是建立多个文件组
create  database  Sales on primary
(
   name=N'Sales',
   filename=N'D:\data\Primary\Sales.mdf',
   size=3MB,
   maxsize=100MB,
   filegrowth=10%
),
filegroup FG1
(
  NAME = N'File1',  
  FILENAME = N'D:\data\FG1\File1.ndf',  
  SIZE = 1MB,  
  MAXSIZE = 100MB,  
  FILEGROWTH = 10%
),
FILEGROUP FG2  
(  
  NAME = N'File2',  
  FILENAME = N'D:\data\FG2\File2.ndf',  
  SIZE = 1MB,  
  MAXSIZE = 100MB,
  FILEGROWTH = 10%  
),
FILEGROUP FG3  
(  
  NAME = N'File3',  
  FILENAME = N'D:\data\FG3\File3.ndf',  
  SIZE = 1MB,  
  MAXSIZE = 100MB,  
  FILEGROWTH = 10%  
)  
LOG ON  
(  
  NAME = N'Sales_Log',  
  FILENAME = N'D:\data\Primary\Sales_Log.ldf',  
  SIZE = 1MB,  
  MAXSIZE = 100MB,  
  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,1),
   OrderDate datetime  not null,
   CustomerID int not null,
   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', 1000)  
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/01/05', 1000)  
INSERT INTO dbo.Orders (OrderDate, 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:\data\FG4\File4.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;