创建分区表

-- 创建分区函数
create partition function PF_Orders_OrderDateRange(datetime) as range right for values ( '1997-01-01', '1998-01-01', '1999-01-01' )
go
-- 创建分区方案
create partition scheme PS_Orders as partition PF_Orders_OrderDateRange to ([primary], [primary], [primary], [primary]) go
-- 创建分区表
create table dbo.Orders ( OrderID int not null  ,CustomerID varchar(10) not null  ,EmployeeID int not null  ,OrderDate datetime not null ) on PS_Orders(OrderDate) go
-- 创建聚集分区索引
create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate) go
-- 为分区表设置主键
alter table dbo.Orders add constraint PK_Orders primary key (OrderID, CustomerID, OrderDate) go
-- 导入数据到分区表
insert into dbo.Orders select OrderID, CustomerID, EmployeeID, OrderDate from dbo.Orders_From_SQL2000_Northwind
--（注：数据来源于 SQL Server 2000 示例数据库）
 go
-- 查看分区表每个分区的数据分布情况
 select partition = $partition.PF_Orders_OrderDateRange(OrderDate) ,rows = count(*) ,minval = min(OrderDate) ,maxval = max(OrderDate) from dbo.Orders group by $partition.PF_Orders_OrderDateRange(OrderDate) order by partition go

--方法2========================================================================

CREATE DATABASE db
 ON(name=db,filename='I:\db.mdf'),
FILEGROUP fg1
( name=fg1,filename='I:\data1.ndf'),
FILEGROUP fg2
( name=fg2,filename='I:\data2.ndf'),
FILEGROUP fg3
( name=fg3,filename='I:\data3.ndf'),
FILEGROUP fg4
( name=fg4,filename='I:\data4.ndf')
LOG ON(name=db_log,filename='I:\db_log.ldf');

 

GO

USE db;
GO

CREATE PARTITION FUNCTION PF_Quarter(int)
AS RANGE RIGHT
FOR VALUES(4,7,10);

CREATE PARTITION SCHEME PS_Quarter
AS PARTITION PF_Quarter
TO(fg1,fg2,fg3,fg4);
GO

CREATE TABLE dbo.tb(id int NOT NULL,date DATETIME NOT NULL,[month] INT NOT NULL)
       ON PS_Quarter([month]);

CREATE UNIQUE CLUSTERED INDEX UIQ_month_tb ON tb(id,[month]);

--插入测试
INSERT INTO dbo.tb(id,date,[month])
    SELECT number,dateadd(day,number,'2009-01-01'),MONTH(dateadd(day,number,'2009-01-01'))
    FROM master.dbo.spt_values WHERE type='p';

--方法3=========================================================================
--添加文件组
ALTER DATABASE t1 Add FileGroup [fg1]
ALTER DATABASE t1 Add FileGroup [fg2]
ALTER DATABASE t1 Add FileGroup [fg3]
ALTER DATABASE t1 Add FileGroup [fg4]
--加不同的文件对用的添加到文件组中
alter database t1 add file(NAME = N'FG1', FILENAME = N'C:\FG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]
alter database t1 add file(NAME = N'FG2', FILENAME = N'C:\FG2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2]
alter database t1 add file(NAME = N'FG3', FILENAME = N'C:\FG3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG3]
alter database t1 add file(NAME = N'FG4', FILENAME = N'C:\FG4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG4]
--为了IO均衡的话，这里面的文件就可以分布在不同的磁盘中了，
--alter database t1 add file(NAME = N'FG2', FILENAME = N'H:\FG2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2]
--H可以是服务器上的磁盘，也可以说其他外接磁盘

 

--创建分区函数
CREATE PARTITION FUNCTION NumPartitionFun(int) AS RANGE left FOR VALUES(100,500,800);

--指定分区方案
create partition scheme NumPartitionScheme as partition NumPartitionFun To ([FG1], [FG2], [FG3], [FG4]);

--完事具备只欠东风

create table nums ( n int ) on NumPartitionScheme(n)

--填充表
declare @max as int, @rc as int;
set @max=1000
set @rc=1;
insert into nums values(1)
while @rc *2<=@max
begin
    insert into nums select n+@rc from nums
    set @rc=@rc*2
end
insert into nums
select n+@rc from nums where n+@rc<=@max

--查询分区情况
select $partition.NumPartitionFun(n) as pn,min(n) as start,max(n) [end] from nums group by $partition.NumPartitionFun(n) order by  $partition.NumPartitionFun(n)