SQLServer2005表分区知识点摘要
1、 准备条件
SQLSERVER2005数据库服务器。
首先在192.168.1.200上创建数据库LeeTest，然后向数据库增加文件组FG1，FG2，FG3，FG4，FG5。再向每个文件组中增加文件。参考SQL语句如下：
CREATE DataBase LeeTest
Alter DataBase LeeTest Add FileGroup [FG1]
Alter DataBase LeeTest Add File(Name=f1,FileName=' F:\LEETESTDATA\f1.ndf',size=100MB,MaxSize=500MB,Filegrowth=10MB) to FileGroup FG1
文件f1.ndf之前在目录F:\LEETESTDATA下不能存在。
同样放在给文件组F2—F5增加文件。一个文件组可以包含多个文件。
2、 创建分区函数
CREATE Partition Function RangeByID(bigint) AS Range left For values(1000,2000,3000)。则表将会分为4个区，第一个区存储的数据是分区列小于等于1000的，第二个区存储的是1001—2000的，第三个区2001—3000的，第四个区3001及以上的。
3、 创建分区架构
CREATE Partition Scheme RangeByIDScheme AS Partition RangeByID To([FG1],[FG2],[FG3],[FG4])。物理存储上的四个区。
4、 创建表及分区
CREATE TABLE [dbo].[OrderInfo](
[SequenceID] [bigint] IDENTITY(1,1) NOT NULL,
[OrderID] [bigint] Primary Key NOT NULL,
[OrderInfo] [varchar](500) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Source] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
) ON [RangeByIDScheme]([OrderID])
现在可以向表里插入5000行数据
Declare @seq int
SET @seq = 0
while(@seq<=5000)
Begin
insert into OrderInfo values(@seq,@seq+'01','00')
set @seq = @seq+1
end
使用如下语句查看分区存储情况：
select $partition.RangeByID(OrderInfo.OrderID) ParNum, count(*) ,Min(OrderID) , Max(OrderID)
from OrderInfo
group by $partition.RangeByID(OrderINfo.OrderID)
order by ParNum
5、 分区更改
拆分分区：
将来OrderInfo表数据量必然会增加，OrderID也会增长，如增长到6000，则可以在分区。分区前，首先要给分区架构指定下一个可用的文件组。
ALTER PARTITION SCHEME RangeByIDScheme NEXT USED [ FG5 ]
拆分分区
ALTER PARTITION FUNCTION RangeByID()
合并分区：
ALTER PARTITION FUNCTION RangeByID()
此时，1001—2000与2001—3000的2个区合并为一个。按照SQLServer2005联机丛书说明：删除一个分区并将该分区中存在的所有值都合并到剩余的某个分区中。RANGE (boundary_value) 必须是一个现有边界值，已删除分区中的值将合并到该值中。如果最初保存 boundary_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组。合并的分区驻留在最初不保存 boundary_value 的文件组中。我的理解，最初保存分区【1001-2000】的文件组FG2将会被从分区方案中除掉，除非ALTER PARTITION SCHEME RangeByIDScheme NEXT USED [ FG2 ]。
6、 索引在分区中的处理
除了对表的数据集进行分区之外，还可以对索引进行分区。使用相同的函数对表及其索引进行分区通常可以优化性能。当索引和表按照相同的顺序使用相同的分区函数和列时，表和索引将对齐。如果在已经分区的表中建立索引，SQL Server 会自动将新索引与该表的分区架构对齐，除非该索引的分区明显不同。当表及其索引对齐后，SQL Server 则可以更有效地将分区移入和移出分区表，因为所有相关的数据和索引都使用相同的算法进行划分。
如果定义表和索引时不仅使用了相同的分区函数，还使用了相同的分区架构，则这些表和索引将被认为是按存储位置对齐。按存储位置对齐的一个优点是，相同边界内的所有数据都位于相同的物理磁盘上。在这种情况下，可以单独在某个时间段内执行备份操作，还可以根据数据的变化在备份频率和备份类型方面改变您的策略。如果连接或收集了相同文件或文件组中的表和索引，则可以发现更多的好处。SQL Server 可以通过在多个分区中并行操作来获益。在按存储位置对齐和多 CPU 的情况下，每个处理器都可以直接处理特定的文件或文件组，而不会与数据访问产生任何冲突，因为所有需要的数据都位于同一个磁盘上。这样，可以并行运行多个进程，而不会相互干扰。
上面的一段话是我摘抄的，我的理解是，如果找不出索引分区的劣处，就使用它。
对已存在数据表的分区解决方案
1、 通过更改聚集索引所在文件组移动表到新文件组(分区)实现现有表分区
以OrderInfo表为例：
如果当初表创建在[Primary]文件组上，且有聚集索引CLS_Index_Source，索引建立在Source列上。
则通过如下SQL分区
drop index CLS_Index_Source on OrderInfo
create clustered index CLS_Index_Source on OrderInfo(Source)
on RangeByIDScheme(OrderId)
如果想把表切换回Primary文件组，则通过如下SQL:
drop index CLS_Index_Source on OrderInfo
create clustered index CLS_Index_Source on OrderInfo(Source)
on [Primary]
2、 现有表普通索引的处理
建议删除。通过重建聚集索引实现数据切换, 不删除普通索引会导致普通索引被重建。
PUser表的分区
我在建立OrderInfo表时，有些地方按照Puser表结构，如主键不是聚集的，聚集索引不再唯一列上等，所以，OrderInfo表的分区方案对Puser表的分区有参考意义。但是，OrderInfo表数据量较小（10000条一下），而Puser表即将达到1000W条，大数据量情况下索引的重建以及分区的变更都会出现难以预料的问题，还需要制定详细的分区方案以及风险规避方法。
建议PuserExtend，PuserLinkinfo使用与Puser表相同的分区架构。
