SQL脚本整理系列一 表分区
浏览数：20 /
时间：2015年06月12日
表分区的目的：
1.把历史数据放到另外一个表里面 可以提高查询效率 当然如果经常查询历史数据和新数据的合并结果集这样做就大大的不好了
2.通过把一个表放到不同的文件，不同的文件再存储到不同的磁盘列阵中，可以提高IO速度 CPU比硬盘快多了
3.提高可用性，一个磁盘坏了，另外一个磁盘上面的文件还能用 这个对我意义不大
4.便于备份 只需要做一个分区的备份就可以了，比如云服务端，数据量比较大的就4个表单，把这四个表放在一个文件里面这样每天备份基本不花什么时间，还原也比较方便，这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
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
