表分区学习--2019/08/04

--1.创建文件组
alter database Test add filegroup [FG_Test_01];
alter database Test add filegroup [FG_Test_02];

--2.创建文件组文件
alter database Test add file(name=N'FG_Test_01',filename=N'D:\Test\FG_TestDATA\FG_Test_01.ndf'
,size=5MB) to filegroup [FG_Test_01];

alter database Test add file(name=N'FG_Test_02',filename=N'D:\Test\FG_TestDATA\FG_Test_02.ndf'
,size=5MB) to filegroup [FG_Test_02];

--3.创建分区函数
create partition function [partition_order_data](datetime)
as range left for values('20140131 23:59:59.997','20170131 23:59:59.997')

--查看
select * from sys.partition_functions

--4.创建分区方案(分区架构)（若要切换分区需预留一个空分区，该例子以PRIMARY 文件组作为第3个空分区）

create partition scheme [scheme_order_date] as partition [partition_order_data]
to ([FG_Test_01],[FG_Test_02],[PRIMARY])
--查看
select * from sys.partition_schemes

--5.在分区方案下建表
create table [dbo].[order_detail](
[order_id] int not null,
[order_name] nvarchar(50),
[order_age] int,
[order_date] datetime
) on [scheme_order_date]([order_date])

--6.插入数据

insert dbo.[order_detail] (order_id,order_name,order_age,order_date)
select o.Id,o.TestName,o.TestAge,o.TestTime
from dbo.TestTab as o