SQL脚本整理系列一 表分区

表分区的目的：

1.把历史数据放到另外一个表里面 可以提高查询效率 当然如果经常查询历史数据和新数据的合并结果集这样做就大大的不好了

2.通过把一个表放到不同的文件，不同的文件再存储到不同的磁盘列阵中，可以提高IO速度   CPU比硬盘快多了

3.提高可用性，一个磁盘坏了，另外一个磁盘上面的文件还能用 这个对我意义不大

4.便于备份 只需要做一个分区的备份就可以了，比如云服务端，数据量比较大的就4个表单，把这四个表放在一个文件里面这样每天备份基本不花什么时间，还原也比较方便，这4个表的数据丢了意义也不大， 没有做测试

 

   分区表的定义大体上分为三个步骤：

1.     定义分区函数
2.     定义分区构架
3.     定义分区表 插入测试数据
4. 【把一个表的数据导入到另外一个表中 这个速度挺快的】

--创建数据库 这些操作可以在图形界面完成
--因为在自己电脑上面测试 所以都放在同一个硬盘上面了  http://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html

CREATE  database  Sales on primary
(
   name=N'Sales',
   filename=N'G:\data\Primary\Sales.mdf',
   size=3MB,
   maxsize=100MB,
   filegrowth=10%
),--创建文件组
filegroup FG1
(
  NAME = N'File1',   
  FILENAME = N'G:\data\FG1\File1.ndf',   
  SIZE = 1MB,   
  MAXSIZE = 100MB,   
  FILEGROWTH = 10% 
),--创建文件组
FILEGROUP FG2   
(   
  NAME = N'File2',   
  FILENAME = N'G:\data\FG2\File2.ndf',   
  SIZE = 1MB,   
  MAXSIZE = 100MB,
  FILEGROWTH = 10%   
),--创建文件组
FILEGROUP FG3   
(   
  NAME = N'File3',   
  FILENAME = N'G:\data\FG3\File3.ndf',   
  SIZE = 1MB,   
  MAXSIZE = 100MB,   
  FILEGROWTH = 10%   
)   
LOG ON   
(   
  NAME = N'Sales_Log',   
  FILENAME = N'G:\data\Primary\Sales_Log.ldf',   
  SIZE = 1MB,   
  MAXSIZE = 100MB,   
  FILEGROWTH = 10%
)
GO

--创建分区函数

USE Sales   
GO
CREATE PARTITION FUNCTION pf_OrderDate (datetime)   
AS RANGE RIGHT  --以后默认就写Right 不要记混了 
FOR VALUES ('2003/01/01', '2004/01/01') 
GO

--建立分区架构 比如把1900-01-01-'2003/01/01'之间的数据存储到FG2里面
-- '2003/01/01'- '2004/01/01'之间的数据也存储到FG2里面
-- '2004/01/01'-  之间的数据也存储到FG3里面

--建立分区架构 比如把1900-01-01-'2003/01/01'之间的数据存储到FG2里面
-- '2003/01/01'- '2004/01/01'之间的数据也存储到FG2里面
-- '2004/01/01'-  之间的数据也存储到FG3里面
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 --订单历史表 可以把不常用的数据放到这个表里面 
--这样对于Orders的查询就会大大提高 有必要的时候再通过Union All 查询所有数据
(
   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   
GO   
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/6/25', 1000)   
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/8/13', 1000)   
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/8/25', 1000)   
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/9/23', 1000)
GO 1000

USE Sales   
GO
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/6/25', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/8/13', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/8/25', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/9/23', 1000)   
GO 1000

GO
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2006/6/25', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2007/8/13', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2008/8/25', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2009/9/23', 1000)   
GO 11000

--查看结果

SELECT * FROM Orders
SELECT * FROM dbo.OrdersHistory

--查看各个分区的数据行数
select $partition.pf_OrderDate(OrderDate) as Patition,COUNT(*) countRow from dbo.Orders
group by $partition.pf_OrderDate(OrderDate)

--检验分区函数
SELECT  $partition.pf_OrderDate('2002') AS '所在分区'
UNION ALL 
SELECT  $partition.pf_OrderDate('2003') AS '所在分区'
UNION ALL 
SELECT  $partition.pf_OrderDate('2004') AS '所在分区'

--把第二部分的数据转存到历史表中 把分区移动到不同的表，效率比较高消耗时间比较少

--目标分区只能是空的；必须都存在；

--必须在同一个文件组内，分区号必须相同（是指两个表公用同一个分区函数的情况下，不共用分区函数就没有范围限制了 ）

--把第二部分的数据转存到历史表中
Use Sales
go
alter table orders switch partition 2 to ordersHistory partition 2
go

--查看结果

--查看结果
SELECT * FROM Orders 
SELECT * FROM dbo.OrdersHistory

 --创建一个新分区

--创建一个新分区 
ALTER PARTITION SCHEME ps_OrderDate
NEXT USED FG3
ALTER PARTITION FUNCTION pf_OrderDate()
SPLIT RANGE ( '2007/01/01')
--检查效果

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2009/9/23', 1000)   
GO 9

--查看各个分区的数据行数
select $partition.pf_OrderDate(OrderDate) as Patition,COUNT(*) countRow from dbo.Orders
group by $partition.pf_OrderDate(OrderDate)

--删除一个分区

--移除分区 
ALTER PARTITION FUNCTION pf_OrderDate()
MERGE RANGE  ( '2004/01/01')

--检查移除效果

--检查移除效果 
SELECT  $partition.pf_OrderDate('2002') AS '所在分区'
UNION ALL 
SELECT  $partition.pf_OrderDate('2003') AS '所在分区'
UNION ALL 
SELECT  $partition.pf_OrderDate('2004') AS '所在分区'
UNION ALL 
SELECT  $partition.pf_OrderDate('2008') AS '所在分区'

-- 移除分区函数 分区方案

a。删除源表 再删除分区函数 分区方案

truncate table name1
drop table name1
DROP PARTITION SCHEME name1
DROP PARTITION FUNCTION name1

b。如果是没有聚集索引的表

可以通过重新建立索引而不引用分区方案的方式来