SQL Server表分区

什么是分区？为什么要使用分区？简单的回答是：为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。通常，创建表是为了存储某种实体（例如客户或销售）的信息，并且每个表只具有描述该实体的属性。一个表对应一个实体是最容易设计和理解的，因此不需要优化这种表的性能、可伸缩性和可管理性，尤其是在表变大的情况下。

大型表是由什么构成的呢？超大型数据库 (VLDB) 的大小以数百 GB 计算，甚至以 TB 计算，但这个术语不一定能够反映数据库中各个表的大小。大型数据库是指无法按照预期方式运行的数据库，或者运行成本或维护成本超出预定维护要求或预算要求的数据库。这些要求也适用于表；如果其他用户的活动或维护操作限制了数据的可用性，则可以认为表非常大。例如，如果性能严重下降，或者每天、每周甚至每个月的维护期间有两个小时无法访问数据，则可以认为销售表非常大。有些情况下，周期性的停机时间是可以接受的，但是通过更好的设计和分区实现，通常可以避免或最大程度地减少这种情况的发生。虽然术语 VLDB 仅适用于数据库，但对分区来说，了解表的大小更重要。

除了大小之外，当表中的不同行集拥有不同的使用模式时，具有不同访问模式的表也可能会影响性能和可用性。尽管使用模式并不总是在变化（这也不是进行分区的必要条件），但在使用模式发生变化时，通过分区可以进一步改善管理、性能和可用性。还以销售表为例，当前月份的数据可能是可读写的，但以往月份的数据（通常占表数据的大部分）是只读的。在数据使用发生变化的类似情况下，或在维护成本随着在表中读写数据的次数增加而变得异常庞大的情况下，表响应用户请求的能力可能会受到影响。相应地，这也限制了服务器的可用性和可伸缩性。

此外，如果以不同的方式使用大量数据集，则需要经常对静态数据执行维护操作。这可能会造成代价高昂的影响，例如性能问题、阻塞问题、备份（空间、时间和运营成本），还可能会对服务器的整体可伸缩性产生负面影响。

分区可以带来什么帮助？当表和索引变得非常大时，分区可以将数据分为更小、更容易管理的部分，从而提供一定的帮助。本文重点介绍横向分区，在横向分区中，大量的行组存储在多个相互独立的分区中。分区集的定义根据需要进行自定义、定义和管理。Microsoft SQL Server 2005 允许您根据特定的数据使用模式，使用定义的范围或列表对表进行分区。SQL Server 2005 还围绕新的表和索引结构设计了几种新功能，为分区表和索引的长期管理提供了大量的选项。

此外，如果具有多个 CPU 的系统中存在一个大型表，则对该表进行分区可以通过并行操作获得更好的性能。通过对各个并行子集执行多项操作，可以改善在极大型数据集（例如数百万行）中执行大规模操作的性能。通过分区改善性能的例子可以从以前版本中的聚集看出。例如，除了聚集成一个大型表外，SQL Server 还可以分别处理各个分区，然后将各个分区的聚集结果再聚集起来。在 SQL Server 2005 中，连接大型数据集的查询可以通过分区直接受益；SQL Server 2000 支持对子集进行并行连接操作，但需要动态创建子集。在 SQL Server 2005 中，已分区为相同分区键和相同分区函数的相关表（如 Order 和 OrderDetails 表）被称为已对齐。当优化程序检测到两个已分区且已对齐的表连接在一起时，SQL Server 2005 可以先将同一分区中的数据连接起来，然后再将结果合并起来。这使 SQL Server 2005 可以更有效地使用具有多个 CPU 的计算机。

sqlserver中创建表分区的流程 如下：

1 要创建文件组，文件组中需要包含文件 所以还需要创建文件。（为了测试可以先只创建文件组，不创建文件 最后向表中插入数据的时候会报错，告知没有分配文件）。

--创建文件组

ALTER DATABASE test ADD FILEGROUP [fg1]
ALTER DATABASE test ADD FILEGROUP [fg2]

 

--为文件组添加文件
ALTER DATABASE test ADD FILE
( NAME = N'FG1', FILENAME = N'C:/test/FG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg1]

ALTER DATABASE test ADD FILE
( NAME = N'FG2', FILENAME = N'C:/test/FG2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2]

--创建分区函数
CREATE PARTITION FUNCTION test_partfunc (int)
AS RANGE LEFT FOR VALUES (100)

--创建分区架构
CREATE PARTITION SCHEME test_partscheme
AS PARTITION test_partfunc TO (fg1, fg2)

--创建表，并指定分区
CREATE TABLE students
(
StudentID int,
StudentName nvarchar(100)
)
ON test_partscheme (StudentID)

由此可见 创建分区架构以后，是可以多个表使用分区架构的。

建立完毕以后可以向表中添加数据。添加完毕以后可以通过以下的sql语句查询每个分区中包涵的记录的条数和分区中最大的和最小的studentid

SELECT $partition.test_partfunc(StudentID) [Partition Number]
, min(o.StudentID) AS [Min StudentID]
, max(o.StudentID) AS [Max StudentID]
, count(*) as [Rows In Partition]
FROM students AS o
GROUP BY $partition.test_partfunc(StudentID)
ORDER BY [Partition Number]

当然，其中还涉及到一个问题就是很多时候我们会以日期来分区，这就导致分区一个一个都会使用，最后一个分区的数据会越来越大，假如我们的本意是想让一个分区就有一个月的数据，针对这种情况的数据。