转载 :sql server分区 http://blog.itpub.net/27099995/viewspace-1081158/
转载:http://blog.itpub.net/27099995/viewspace-1081158/
在 sql server 2005 之前不提供分区表，但可以用其他方式建立“分区表”，sql server 2005之后提供了分区表，这使得我们在处理超大容量数据库性能方面有了新的选择，通过建立分区表，可以把数据存储在不同的物理硬盘上，在多cpu及多硬盘的服务器上，有效的利用了i/o并行处理，使得不至于存取数据时卡死在某一个物理硬盘上。
建立分区表，主要有三个步骤：
1、创建分区函数以指定如何分区使用该函数的表或索引。
2、创建分区方案以指定分区函数的分区在文件组上的位置。
3、创建使用分区方案的表或索引。
现在以实际的操作
--【添加文件组】
ALTER DATABASE cargobookingdb ADD FILEGROUP [caogo2009]
ALTER DATABASE cargobookingdb ADD FILEGROUP [caogo2010];
ALTER DATABASE cargobookingdb ADD FILEGROUP [caogo2011];
ALTER DATABASE cargobookingdb ADD FILEGROUP [caogo2012];
ALTER DATABASE cargobookingdb ADD FILEGROUP [caogo2013];
ALTER DATABASE cargobookingdb ADD FILEGROUP [caogo2014];
--【添加文件并把其指向指定文件组】
ALTER DATABASE cargobookingdb
ADD FILE
(
NAME = caogo2008,
FILENAME ='E:sqlserver_dataDW_Data2008.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB
)
TO FILEGROUP caogo2008
--【删除文件】
alter database cargobookingdb remove file [caogo2014]
--如果文件不为空时会报错
消息 5042，级别 16，状态 1，第 1 行
无法删除 文件'caogo2014'，因为它不为空。
--【修改文件】
ALTER DATABASE cargobookingdb
MODIFY FILE
(
NAME = caogo2014,
SIZE = 2MB
);
--【移动文件】
ALTER DATABASE cargobookingdb
MODIFY FILE
(
NAME = caogo2014,
filename = 'E:sqlserver_dataDW_Data2014.ndf'
);
---文件 'caogo2014' 在系统目录中已修改。新路径将在数据库下次启动时使用。
二、创建分区函数及分区方案(架构)--此步骤为核心部分
--【创建分区函数】
Create PARTITION FUNCTION PARTCARGODBHISTORY(DATETIME) AS RANGE LEFT FOR VALUES
(
'20090101 23:59:59.997',
'20100101 23:59:59.997',
'20110101 23:59:59.997',
'20120101 23:59:59.997',
'20130101 23:59:59.997',
'20140101 23:59:59.997'
);
--【创建分区方案】
CREATE PARTITION SCHEME [cargodb]
AS
PARTITION PARTCARGODBHISTORY TO
(
[caogo2008],
[caogo2009],
[caogo2010],
[caogo2011],
[caogo2012],
[caogo2013],
[caogo2014]
);
3.--创建分区表,通过分区键值testTime和分区方案[cargodb]结合。
CREATE TABLE test(
[Id] [int] IDENTITY(1,1) NOT NULL, --自动增长
[Name] [varchar](16) NOT NULL,
[testTime] [datetime] NOT NULL,
)
on cargodb (testTime)
三、为表插入数据，查看结果 --这里我们插入100W数据
insert test ([Name],[testTime]) values ('张三','2007-1-1')
insert test ([Name],[testTime]) values ('张三','2008-1-1')
insert test ([Name],[testTime]) values ('李四','2009-12-30')
insert test ([Name],[testTime]) values ('王五','2009-1-1')
insert test ([Name],[testTime]) values ('钱六','2009-4-1')
insert test ([Name],[testTime]) values ('赵七','2010-1-1')
insert test ([Name],[testTime]) values ('张三','2010-6-1')
insert test ([Name],[testTime]) values ('李四','2010-12-30')
insert test ([Name],[testTime]) values ('王五','2011-8-1')
insert test ([Name],[testTime]) values ('钱六','2012-9-1')
insert test ([Name],[testTime]) values ('赵七','2013-1-1')
insert test ([Name],[testTime]) values ('张三','2012-11-1')
insert test ([Name],[testTime]) values ('张三','2012-12-1')
insert test ([Name],[testTime]) values ('李四','2013-12-30')
insert test ([Name],[testTime]) values ('王五','2014-1-1')
insert test ([Name],[testTime]) values ('王五','2014-12-1')
insert test ([Name],[testTime]) values ('王五','2015-12-1')
insert test ([Name],[testTime]) values ('王五','2016-12-1')
--【查看分区信息】
select $PARTITION.PARTCARGODBHISTORY(testTime) as 分区编号,
count(id) as 记录数 ,
MIN(testTime) as 最早,
MAX(testTime) as 最近
from test
group by $PARTITION.PARTCARGODBHISTORY(testTime)
可以看到，有test被分到7个分区，也即被存储在7个物理文件上。
到此，分区表建立完成。
给一个记录大一些的表，两张表都有两百多万条记录。
tab_awb1 --没有分区
tab_awb --按照上面
Set Statistics IO ON
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
----清楚缓存，效果更明显，但是不建议在生产库中持续，不然有意外产生
select * from tab_awb1 where FlightDate1='2010-10-1'
(1298 行受影响)表 'TBL_AWB1'。扫描计数 5，逻辑读取 158820 次，物理读取 1544 次，预读 157493 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
----用时27秒
再看：
select * from tab_awb where FlightDate1='2010-10-1'
表 'tab_awb'。扫描计数 1，逻辑读取 84 次，物理读取 3 次，预读 81 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
----用时不到1秒