【疑惑五】分区表

好吧。。
我承认我不懂分区表。
请问 
分区表使用情况是那些。。
举个例子看看。。

就是分区表用的场合、。

49 个解决方案

#1

sf不留

#2

db

#3

引用 1 楼 feixianxxx 的回复:

sf不留

懒得理你

#4

引用 3 楼 fredrickhu 的回复:

引用 1 楼 feixianxxx 的回复:
sf不留


懒得理你

哼,哼

#5

不懂，帮顶

#6

7、文件和文件组的填充策略：
   文件组对组内的所有文件都是用按比例填充策略，将数据写入文件组时，数据库引擎会根据文件中的可用空间量将一定比例的数据写入文件组中的每个文件，而不是将所有数据先写满第一个文件，然后再写入下一个文件。例如：如果文件f1有100MB可用空间，文件f2有200MB可用空间，则从文件f1中分配一个区，从f2中分配两个区，一次类推，这样，两个文件几乎同时填满。
文件组中的所有文件一满，数据库引擎就自动按照循环方式一次扩展一个文件，以容纳更多的数据。例如：某个文件组由3个文件组成，都设置为自动增长。当文件组中所有文件的空间都已用完时，只扩展第一个文件，当第一个文件已满，无法再向文件组中写入更多数据时，将扩展第二个文件，第二个文件已满，将扩展第三个文件。第三个满，则再扩展第一个文件。
文件和文件组还允许数据布局，因为可以在特定的文件组中创建表，这样可以改善性能，因为可以将特定表的所有I/O都定先各一个特定的磁盘
8、文件和文件组的设计规则：
   一个文件或者文件组不能由多个数据库使用；一个文件只能是一个文件组的成员；数据和事务日志信息不能属于同一个文件或文件组；事务日志文件不能属于任何文件组。
使用文件和文件组时的一些建议：
大多数数据库在只有单个数据文件和单个事务日志文件的情况下性能良好。
如果使用多个文件，应当为附件文件创造第二个文件组，并将其设置为默认文件组，这样，主文件将只包含系统表和对象。
要使性能最大化，应当在尽可能多的不同的可用本地物理磁盘上创建文件或文件组，应当将争夺空间最激烈 的对象置于不同的文件组中。
使用文件组将对象放置在特定的物理磁盘上。
将在同一连接查询中使用的不同表置于不同表置于不同的文件组中。
由于采用并行I/O对连接查询数据进行搜索，所以性能得以改善。
将最常访问的表和属于这些表的非聚集索引置于不同的文件组中。
不要将事务日志文件置于其中已有其他文件和文件组的物理磁盘上。

#7

不敢搬门弄斧

#8

SQL Server 2005 分区表——滑动窗口方案实践：采用滑动窗口机制，把分区表的分区依次移入到另一个分区表。值得一提的是，本文示例涉及了 SQL 分区表的方方面面：建立分区函数（partition function）、分区方案（partition scheme）；分区表创建；分区函数拆分、合并；分区表分区切换（partition swtich）；分区索引（partition index）等诸多内容。 

建立分区表 Orders 
建立分区表 OrdersArchive （用来归档 Orders 表中不再活跃的数据） 
查看分区表分区函数的分区范围 
获取 dbo.sp_partition_range sp_partition_range代码 

查看分区表每个分区的数据分布情况 
运用滑动窗口机制，把分区表 Orders 分区数据迁移入 OrdersArchive 
窗口滑动的步骤： 1. 在 OrdersArchive 分区表增加一个空闲分区。 2. 移动 Orders 一个分区到相应的 OrdersArchive 分区。 3. 删除 Orders 中的空闲分区。 

移动订单日期为 1996 年的分区数据： 

移动订单日期为 1997 年的分区数据： 

移动订单日期为 1998 年的分区数据： 

经过以上三次移动，我们已经把分区表 Orders 中的数据全部移动到 OrdersArchive 中了。同时我们注意到：在这个窗口滑动过程中，代码中只有三处是变化的（有规律）： “split range('2000-01-01')”。 “merge range('1998-01-01')”。 “to dbo.OrdersArchive partition 4”。这就为程序化处理，提供了条件。 

帖一些东西给蒂哥,

#9

引用 3 楼 fredrickhu 的回复:

引用 1 楼 feixianxxx 的回复:
sf不留


懒得理你

小气 。。。

#10

引用 6 楼 herowang 的回复:

7、文件和文件组的填充策略：
  文件组对组内的所有文件都是用按比例填充策略，将数据写入文件组时，数据库引擎会根据文件中的可用空间量将一定比例的数据写入文件组中的每个文件，而不是将所有数据先写满第一个文件，然后再写入下一个文件。例如：如果文件f1有100MB可用空间，文件f2有200MB可用空间，则从文件f1中分配一个区，从f2中分配两个区，一次类推，这样，两个文件几乎同时填满。
文件组中的所有文件一满，数据库引擎就自动按照循环方式一次扩展一个文件，以容纳更多的数据。例如：某个文件组由3个文件组成，都设置为自动增长。当文件组中所有文件的空间都已用完时，只扩展第一个文件，当第一个文件已满，无法再向文件组中写入更多数据时，将扩展第二个文件，第二个文件已满，将扩展第三个文件。第三个满，则再扩展第一个文件。
文件和文件组还允许数据布局，因为可以在特定的文件组中创建表，这样可以改善性能，因为可以将特定表的所有I/O都定先各一个特定的磁盘
8、文件和文件组的设计规则：
  一个文件或者文件组不能由多个数据库使用；一个文件只能是一个文件组的成员；数据和事务日志信息不能属于同一个文件或文件组；事务日志文件不能属于任何文件组。
使用文件和文件组时的一些建议：
大多数数据库在只有单个数据文件和单个事务日志文件的情况下性能良好。
如果使用多个文件，应当为附件文件创造第二个文件组，并将其设置为默认文件组，这样，主文件将只包含系统表和对象。
要使性能最大化，应当在尽可能多的不同的可用本地物理磁盘上创建文件或文件组，应当将争夺空间最激烈 的对象置于不同的文件组中。
使用文件组将对象放置在特定的物理磁盘上。
将在同一连接查询中使用的不同表置于不同表置于不同的文件组中。
由于采用并行I/O对连接查询数据进行搜索，所以性能得以改善。
将最常访问的表和属于这些表的非聚集索引置于不同的文件组中。
不要将事务日志文件置于其中已有其他文件和文件组的物理磁盘上。

影子哥。。
你简明生动 活泼跟我说说 用什么地方 一长段的看不下去

#11

学习下先

#12

MS SQL Server：分区表、分区索引 详解(2009-05-25 12:55:29)标签：it   分类：sql 
1. 分区表简介
使用分区表的主要目的，是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。

        大型表：数据量巨大的表。
        访问模式：因目的不同，需访问的不同的数据行集，每种目的的访问可以称之为一种访问模式。

分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；另一方面，对于如果具有多个CPU的系统，分区可以是对表的操作通过并行的方式进行，这对于提升性能是非常有帮助的。

注意：只能在 SQL Server Enterprise Edition 中创建分区函数。只有 SQL Server Enterprise Edition 支持分区。
2. 创建分区表或分区索引的步骤
可以分为以下步骤：
1. 确定分区列和分区数
2. 确定是否使用多个文件组
3. 创建分区函数
4. 创建分区架构（Schema）
5. 创建分区表
6. 创建分区索引

下面详细描述的创建分区表、分区索引的步骤。
2.1. 确定分区列和分区数
在开始做分区操作之前，首先要确定待分区表的访问模式，该模式决定了什么列适合做分区键。例如，对于销售数据，一般会先根据日期把数据范围限定在一个范围内，然后在这个基础上做进一步的查询，这样，就可以把日期作为分区列。

确定了分区列之后，需要进一步确定分区数，亦即分区表中需要包含多少数据，每个分区的数据应该限定在哪个范围。

2.2. 确定是否使用多个文件组
为了有助于优化性能和维护，应该使用文件组分离数据。一般情况下，如果经常对分区的整个数据集操作，则文件组数最好与分区数相同，并且这些文件组通常应该位于不同的磁盘上，再配合多个CPU，则SQL Server 可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。

2.3. 创建分区函数
分区函数用于定义分区的边界条件，创建分区函数的语法如下：
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] )
[ ; ]

参数说明：
        partition_function_name
是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。

        input_parameter_type
是用于分区的列的数据类型。当用作分区列时，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR 用户定义数据类型外，所有数据类型均有效。

实际列（也称为分区列）是在 CREATE TABLE 或 CREATE INDEX 语句中指定的。

        boundary_value
为使用 partition_function_name 的已分区表或索引的每个分区指定边界值。如果 boundary_value 为空，则分区函数使用 partition_function_name 将整个表或索引映射到单个分区。只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的一个分区列。

boundary_value 是可以引用变量的常量表达式。这包括用户定义类型变量，或函数以及用户定义函数。它不能引用 Transact-SQL 表达式。boundary_value 必须与 input_parameter_type 中提供的数据类型相匹配或者可隐式转换为该数据类型，并且如果该值的大小和小数位数与 input_parameter_type 中相应的值的大小和小数位数不匹配，则在隐式转换过程中该值不能被截断。

注意：
如果 boundary_value 包含 datetime 或 smalldatetime 文字值，则为这些文字值在计算时假设 us_english 是会话语言。不推荐使用此行为。要确保分区函数定义对于所有会话语言都具有预期的行为，建议使用对于所有语言设置都以相同方式进行解释的常量，例如 yyyymmdd 格式；或者将文字值显式转换为特定样式。有关详细信息，请参阅编写国际化 Transact-SQL 语句。若要确定服务器的语言会话，请运行 SELECT @@LANGUAGE。

        ...n
指定 boundary_value 提供的值的数目，不能超过 999。所创建的分区数等于 n + 1。不必按顺序列出各值。如果值未按顺序列出，则 Microsoft SQL Server 2005 数据库引擎将对它们进行排序，创建函数并返回一个警告，说明未按顺序提供值。如果 n 包括任何重复的值，则数据库引擎将返回错误。

        LEFT | RIGHT
指定当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。

创建分区函数示例：
CREATE PARTITION FUNCTION PF_Left(int)
AS RANGE LEFT
FOR VALUES(10, 20)
GO

CREATE PARTITION FUNCTION PF_Right(int)
AS RANGE LEFT
FOR VALUES(10, 20)
GO

PF_Left 和 PF_Right 分区函数的区分：
分区函数        分区1        分区2        分区3
PF_Left        <= 10         > 10 and <= 20        > 20
PF_Right        < 10        >= 10 and < 20        >= 20

2.4. 创建分区架构（Schema）
创建分区函数后，必须将其与分区架构（Schema）相关联，以便将分区定向至特定的文件组。定义分区架构师，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。

创建分区架构的语法如下：
GOCREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

参数：
        partition_scheme_name
分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。

        partition_function_name
使用分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。partition_function_name 必须已经存在于数据库中。

        ALL
指定所有分区都映射到在 file_group_name 中提供的文件组，或映射到主文件组（如果指定了 [PRIMARY]。如果指定了 ALL，则只能指定一个 file_group_name。

        file_group_name | [ PRIMARY ] [ ,...n]
指定用来持有由 partition_function_name 指定的分区的文件组的名称。file_group_name 必须已经存在于数据库中。

如果指定了 [PRIMARY]，则分区将存储于主文件组中。如果指定了 ALL，则只能指定一个 file_group_name。分区分配到文件组的顺序是从分区 1 开始，按文件组在 [,...n] 中列出的顺序进行分配。在 [,...n] 中，可以多次指定同一个 file_group_name。如果 n 不足以拥有在 partition_function_name 中指定的分区数，则 CREATE PARTITION SCHEME 将失败，并返回错误。

如果 partition_function_name 生成的分区数少于文件组数，则第一个未分配的文件组将标记为 NEXT USED，并且出现显示命名 NEXT USED 文件组的信息。如果指定了 ALL，则单独的 file_group_name 将为该 partition_function_name 保持它的 NEXT USED 属性。如果在 ALTER PARTITION FUNCTION 语句中创建了一个分区，则 NEXT USED 文件组将再接收一个分区。若要再创建一个未分配的文件组来拥有新的分区，请使用 ALTER PARTITION SCHEME。

在 file_group_name[ 1,...n] 中指定主文件组时，必须像在 [PRIMARY] 中那样分隔 PRIMARY，因为它是关键字。

创建分区架构示例：
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);
GO

2.5. 创建分区表
定义了分区函数（逻辑结构）和分区架构（物理结构）后，既可以创建分区表来利用它们。分区表定义应使用的分区架构，而分区架构又定义其使用的分区函数。要将这三者结合起来，必须指定应用于分区函数的列 。范围分区始终只映射到表中的一列。

CREATE TABLE 语法如下：
CREATE TABLE
    [ database_name . [ schema_name ] . | schema_name . ] table_name
        ( { <column_definition> | <computed_column_definition> }
        [ <table_constraint> ] [ ,...n ] )
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup
        | "default" } ]
    [ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]

示例如下：
CREATE TABLE myRangePT1
(
        ID        int not null,
        AGE int,
        PRIMARY KEY (ID)
) ON myRangePS1(myRangePF1)
GO


2.6. 创建分区索引
索引对于提高查询性能非常有效，因此，一般应该考虑应该考虑为分区表建立索引，为分区表建立索引与为普通表建立索引的语法一直，但是，其行为与普通索引有所差异。

默认情况下，分区表中创建的索引使用与分区表相同分区架构和分区列，这样，索引将于表对齐。将表与其索引对齐，可以使管理工作更容易进行，对于滑动窗口方案尤其如此。若要启动分区切换，表的所有索引都必须对齐。

在创建索引时，也可以指定不同的分区方案（Schema）或单独的文件组（FileGroup）来存储索引，这样SQL Server 不会将索引与表对齐。

在已分区的表上创建索引（分区索引）时，应该注意以下事项：
        唯一索引
建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此限制将使SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用DML触发器，而不是强制实现唯一性。

        非唯一索引
对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下SQL Server 将在聚集索引列中添加分区依据列。
对非唯一的非聚集索引进行分区时，默认情况下SQL Server 将分区依据列添加为索引的包含性列，以确保索引与基表对齐，若果索引中已经存在分区依据列，SQL Server 将不会像索引中添加分区依据列。

3. 分区操作
分区适用于可以缩放的大型表，所以随着时间和环境的变化，就会产生对分区的拆分、合并、移动的需求。
3.1. 拆分与合并分区
        通过拆分或合并边界值更改分区函数。通过执行 ALTER PARTITION FUNCTION，可以将使用分区函数的任何表或索引的某个分区拆分为两个分区，也可以将两个分区合并为一个分区。
       
        注意：多个表或索引可以使用同一分区函数。ALTER PARTITION FUNCTION 在单个事务中影响所有这些表或索引。
       
ALTER PARTITION FUNCTION 语法如下：
ALTER PARTITION FUNCTION partition_function_name()
{
    SPLIT RANGE ( boundary_value )
  | MERGE RANGE ( boundary_value )
} [ ; ]

参数说明：
        partition_function_name
要修改的分区函数的名称。

        SPLIT RANGE ( boundary_value )
在分区函数中添加一个分区。boundary_value 确定新分区的范围，因此它必须不同于分区函数的现有边界范围。根据 boundary_value，Microsoft SQL Server 2005 数据库引擎将某个现有范围拆分为两个范围。在这两个范围中，新 boundary_value 所在的范围被视为是新分区。

重要提示：
文件组必须处于联机状态，并且必须由使用此分区函数的分区方案标记为 NEXT USED，以保存新分区。在 CREATE PARTITION SCHEME 语句中，将把文件组分配给分区。如果 CREATE PARTITION SCHEME 语句分配了多余的文件组（在 CREATE PARTITION FUNCTION 语句中创建的分区数少于用于保存它们的文件组），则存在未分配的文件组，分区方案将把其中的某个文件组标记为 NEXT USED。该文件组将保存新的分区。如果分区方案未将任何文件组标记为 NEXT USED，则必须使用 ALTER PARTITION SCHEME 添加一个文件组或指定一个现有文件组来保存新分区。可以指定已保存分区的文件组来保存附加分区。由于一个分区函数可以参与多个分区方案，因此所有使用分区函数（您向其中添加了分区）的分区方案都必须拥有一个 NEXT USED 文件组。否则，ALTER PARTITION FUNCTION 将失败并出现错误，该错误显示缺少 NEXT USED 文件组的一个或多个分区方案。

        MERGE [ RANGE ( boundary_value) ]
删除一个分区并将该分区中存在的所有值都合并到剩余的某个分区中。RANGE (boundary_value) 必须是一个现有边界值，已删除分区中的值将合并到该值中。如果最初保存 boundary_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组。合并的分区驻留在最初不保存 boundary_value 的文件组中。boundary_value 是一个可以引用变量（包括用户定义类型变量）或函数（包括用户定义函数）的常量表达式。它无法引用 Transact-SQL 表达式。boundary_value 必须匹配或可以隐式转换为其对应列的数据类型，并且当值的大小和小数位数不匹配其对应 input_parameter_type 时，将无法在隐式转换过程中被截断。

示例：
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]



备注：
ALTER PARTITION FUNCTION 在单个原子操作中对使用该函数的任何表和索引进行重新分区。但该操作在脱机状态下进行，并且根据重新分区的范围，可能会消耗大量资源。

ALTER PARTITION FUNCTION 只能用于将一个分区拆分为两个分区，或将两个分区合并为一个分区。若要更改其他情况下对表进行分区方法（例如，将 10 个分区合并为 5 个分区），可以尝试使用以下任何选项。根据系统配置，这些选项可能在资源消耗方面有所不同：

        使用所需的分区函数创建一个新的已分区表，然后使用 INSERT INTO...SELECT FROM 语句将旧表中的数据插入新表。

        为堆创建分区聚集索引。
注意：
删除已分区的聚集索引将产生分区堆。

        通过将 Transact-SQL CREATE INDEX 语句与 DROP EXISTING = ON 子句一起使用来删除并重新生成现有的已分区索引。

        执行一系列 ALTER PARTITION FUNCTION 语句。

ALTER PARITITION FUNCTION 所影响的全部文件组都必须处于联机状态。
如果使用分区函数的任何表中存在已禁用的聚集索引，ALTER PARTITION FUNCTION 都将失败。
Microsoft SQL Server 2005 不对修改分区函数提供复制支持。必须在订阅数据库中手动应用对发布数据库中的分区函数的更改。

3.2. 移动分区数据
        可以使用 ALTER TABLE ....... SWITCH 语句按一下方式快速有效地移动数据子集：
        将某个表中的数据移动到另一个表中；
        将某个表作为分区添加到现存的已分区表中；
        将分区从一个已分区表切换到另一个已分区表；
        删除分区以形成单个表。

使用这些方案移动数据时，无论集合有多大，此方案都能快速有效地进行传输，因为操作并不以物理方式移动数据，只有关于存储位置的元数据会从一个分区变为另一个分区。

        ALTER TABLE .... SWITCH 的语法如下：
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
        SWITCH [ PARTITION source_partition_number_expression_r ]
    TO [schema_name].target_table
        [ PARTITION target_partition_number_expression_r ]
}
[ ; ]

哇,先留在这里,再收藏,以后俺们自己慢慢看,呵呵

#13

alter database [hutai]
add filegroup [FG20070630]
alter database [hutai]
add filegroup [FG20071231]
alter database [hutai]
add filegroup [FG20080630]
alter database [hutai]
add filegroup [FG20081231]
alter database [hutai]
add filegroup [FG20090630]
go
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20070630',
--创建文件组
FILENAME = N'E:\MealRecords\MealRecord0706.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20070630]
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20071231',
FILENAME = N'E:\MealRecords\MealRecord0712.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20071231]
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20080630',
FILENAME = N'E:\MealRecords\MealRecord0806.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20080630]
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20081231',
FILENAME = N'E:\MealRecords\MealRecord0812.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20081231]
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20090630',
FILENAME = N'E:\MealRecords\MealRecord0906.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20090630]

--创建分区函数
CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('2007-06-30 23:59:59.997',
            '2007-12-31 23:59:59.997',
            '2008-06-30 23:59:59.997',
            '2008-12-31 23:59:59.997')

--创建分区架构
CREATE PARTITION SCHEME [MealSMSPS] 
AS PARTITION OrderDateRangePFN
TO ([FG20070630],[FG20071231],[FG20080630],[FG20081231],[FG20090630])
--创建分区表
CREATE TABLE [MealRecords] (
 [id] [int] NOT NULL ,
 [ver_id] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [pos_sequ] [int] NULL ,
 [card_sequ] [int] NOT NULL ,
 [opcard_id] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [clock_id] [int] NOT NULL ,
 [emp_id] [nvarchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [card_id] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [sign_time] [datetime] NOT NULL ,
 [mark] [tinyint] NULL ,
 [flag] [tinyint] NULL ,
 [card_times] [int] NOT NULL ,
 [card_consume] [money] NOT NULL ,
 [card_balance] [money] NOT NULL ,
 [kind] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [passed] [bit] NULL ,
 [mealtype] [smallint] NULL ,
 [op_ymd] [datetime] NULL ,
 [difine_sequ] [int] NULL ,
 [op_user] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [id_value] [int] NULL ,
 [other_id] [int] NULL ,
 CONSTRAINT [PK_MealRecords_new] PRIMARY KEY  CLUSTERED 
 (
  [sign_time],
  [card_id],
  [card_times],
  [card_balance],
  [card_sequ]
 ) WITH  FILLFACTOR = 95  ON [PRIMARY] 
)ON MealSMSPS([sign_time])

 导入历史记录：922W，数据分布主要集中在2007.6月，其它月份记录比较平均。
数据库服务器为普通PC，无多硬盘和多CPU。

                        分区表测试结果

时间跨度
 原单一表
 分区表
 查询记录量
   
  
  
 


查询SQL语句样本：

SELECT M.emp_id, M.card_id, E.emp_fname,D.Depart_id,D.depart_name, 

        M.clock_id, M.Pos_Sequ, M.Card_Sequ, M.sign_time, M.flag, 

        M.card_times, M.card_consume, M.card_balance, M.kind ,M.MealType 

FROM MealRecords AS M inner JOIN (Employee AS E inner join Departs D on E.depart_id=D.depart_id) ON M.emp_id=E.emp_id 

WHERE (M.sign_time >= '2007-04-01') And  (M.sign_time <= '2007-06-30') 

在这里看到的，看懂一点

#14

好东西

#15

--=========================================   
-- 转换为分区表   
--=========================================   
-- 1. 创建分区函数   
--    a. 适用于存储历史存档记录的分区表的分区函数   
DECLARE @dt datetime   
SET @dt = '20020101'  
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)   
AS RANGE RIGHT  
FOR VALUES(   
    @dt,   
    DATEADD(Year, 1, @dt))   
    
--    b. 适用于存储历史记录的分区表的分区函数   
--DECLARE @dt datetime   
SET @dt = '20000101'  
CREATE PARTITION FUNCTION PF_History(datetime)   
AS RANGE RIGHT  
FOR VALUES(   
    @dt,   
DATEADD(Month, 1, @dt),   
DATEADD(Month, 2, @dt),   
DATEADD(Month, 3, @dt),   
DATEADD(Month, 4, @dt),   
DATEADD(Month, 5, @dt),   
DATEADD(Month, 6, @dt),   
DATEADD(Month, 7, @dt),   
DATEADD(Month, 8, @dt),   
DATEADD(Month, 9, @dt),   
DATEADD(Month, 10, @dt),   
DATEADD(Month, 11, @dt),   
DATEADD(Month, 12, @dt))   
GO   
    
-- 2. 创建分区架构   
--    a. 适用于存储历史存档记录的分区表的分区架构   
CREATE PARTITION SCHEME PS_HistoryArchive   
AS PARTITION PF_HistoryArchive   
TO([PRIMARY], [PRIMARY], [PRIMARY])   
    
--    b. 适用于存储历史记录的分区表的分区架构   
CREATE PARTITION SCHEME PS_History   
AS PARTITION PF_History   
TO([PRIMARY], [PRIMARY],   
    [PRIMARY], [PRIMARY], [PRIMARY],   
    [PRIMARY], [PRIMARY], [PRIMARY],   
    [PRIMARY], [PRIMARY], [PRIMARY],   
    [PRIMARY], [PRIMARY], [PRIMARY])   
GO   
    
-- 3. 删除索引   
--    a. 删除存储历史存档记录的表中的索引   
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID   
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID   
    
--    b. 删除存储历史记录的表中的索引   
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductID   
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID   
GO   
    
-- 4. 转换为分区表   
--    a. 将存储历史存档记录的表转换为分区表   
ALTER TABLE Production.TransactionHistoryArchive   
    DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID   
    WITH(   
        MOVE TO PS_HistoryArchive(TransactionDate))   
    
--    b.将存储历史记录的表转换为分区表   
ALTER TABLE Production.TransactionHistory   
    DROP CONSTRAINT PK_TransactionHistory_TransactionID   
    WITH(   
        MOVE TO PS_History(TransactionDate))   
GO   
    
-- 5. 恢复主键   
--    a. 恢复存储历史存档记录的分区表的主键   
ALTER TABLE Production.TransactionHistoryArchive   
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID   
        PRIMARY KEY CLUSTERED(   
            TransactionID,   
            TransactionDate)   
    
--    b. 恢复存储历史记录的分区表的主键   
ALTER TABLE Production.TransactionHistory   
    ADD CONSTRAINT PK_TransactionHistory_TransactionID   
        PRIMARY KEY CLUSTERED(   
            TransactionID,   
            TransactionDate)   
GO   
-- 6. 恢复索引   
--    a. 恢复存储历史存档记录的分区表的索引   
CREATE INDEX IX_TransactionHistoryArchive_ProductID   
    ON Production.TransactionHistoryArchive(   
        ProductID)   
    
CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID   
    ON Production.TransactionHistoryArchive(   
        ReferenceOrderID,   
        ReferenceOrderLineID)   
    
--    b. 恢复存储历史记录的分区表的索引   
CREATE INDEX IX_TransactionHistory_ProductID   
    ON Production.TransactionHistory(   
        ProductID)   
    
CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID   
    ON Production.TransactionHistory(   
        ReferenceOrderID,   
        ReferenceOrderLineID)   
GO   
-- 7. 查看分区表的相关信息   
SELECT  
    SchemaName = S.name,   
    TableName = TB.name,   
    PartitionScheme = PS.name,   
    PartitionFunction = PF.name,   
    PartitionFunctionRangeType = CASE  
            WHEN boundary_value_on_right = 0 THEN 'LEFT'  
            ELSE 'RIGHT' END,   
    PartitionFunctionFanout = PF.fanout,   
    SchemaID = S.schema_id,   
    ObjectID = TB.object_id,   
    PartitionSchemeID = PS.data_space_id,   
    PartitionFunctionID = PS.function_id   
FROM sys.schemas S   
    INNER JOIN sys.tables TB   
        ON S.schema_id = TB.schema_id   
    INNER JOIN sys.indexes IDX   
        on TB.object_id = IDX.object_id   
            AND IDX.index_id < 2   
    INNER JOIN sys.partition_schemes PS   
        ON PS.data_space_id = IDX.data_space_id   
    INNER JOIN sys.partition_functions PF   
        ON PS.function_id = PF.function_id   
GO   
    
--=========================================   
-- 移动分区表数据   
--=========================================   
-- 1. 为存储历史存档记录的分区表增加分区, 并接受从历史记录分区表移动过来的数据   
--    a. 修改分区架构, 增加用以接受新分区的文件组   
ALTER PARTITION SCHEME PS_HistoryArchive   
NEXT USED [PRIMARY]   
    
--    b. 修改分区函数, 增加分区用以接受从历史记录分区表移动过来的数据   
DECLARE @dt datetime   
SET @dt = '20030901'  
ALTER PARTITION FUNCTION PF_HistoryArchive()   
SPLIT RANGE(@dt)   
    
--    c. 将历史记录表中的过期数据移动到历史存档记录表中   
ALTER TABLE Production.TransactionHistory   
    SWITCH PARTITION 2   
        TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)   
    
--    d. 将接受到的数据与原来的分区合并   
ALTER PARTITION FUNCTION PF_HistoryArchive()   
MERGE RANGE(@dt)   
GO   
    
-- 2. 将存储历史记录的分区表中不包含数据的分区删除, 并增加新的分区以接受新数据   
--    a. 合并不包含数据的分区   
DECLARE @dt datetime   
SET @dt = '20030901'  
ALTER PARTITION FUNCTION PF_History()   
MERGE RANGE(@dt)   
    
--    b.  修改分区架构, 增加用以接受新分区的文件组   
ALTER PARTITION SCHEME PS_History   
NEXT USED [PRIMARY]   
    
--    c. 修改分区函数, 增加分区用以接受新数据   
SET @dt = '20041001'  
ALTER PARTITION FUNCTION PF_History()   
SPLIT RANGE(@dt)   
GO   
    
    
--=========================================   
-- 清除历史存档记录中的过期数据   
--=========================================   
-- 1. 创建用于保存过期的历史存档数据的表   
CREATE TABLE Production.TransactionHistoryArchive_2001_temp(   
    TransactionID int NOT NULL,   
    ProductID int NOT NULL,   
    ReferenceOrderID int NOT NULL,   
    ReferenceOrderLineID int NOT NULL  
        DEFAULT ((0)),   
    TransactionDate datetime NOT NULL  
        DEFAULT (GETDATE()),   
    TransactionType nchar(1) NOT NULL,   
    Quantity int NOT NULL,   
    ActualCost money NOT NULL,   
    ModifiedDate datetime NOT NULL  
        DEFAULT (GETDATE()),   
    CONSTRAINT PK_TransactionHistoryArchive_2001_temp_TransactionID   
        PRIMARY KEY CLUSTERED(   
            TransactionID,   
            TransactionDate)   
)   
    
-- 2. 将数据从历史存档记录分区表移动到第步创建的表中   
ALTER TABLE Production.TransactionHistoryArchive   
    SWITCH PARTITION 1   
        TO Production.TransactionHistoryArchive_2001_temp   
    
-- 3. 删除不再包含数据的分区   
DECLARE @dt datetime   
SET @dt = '20020101'  
ALTER PARTITION FUNCTION PF_HistoryArchive()   
MERGE RANGE(@dt)   
    
-- 4. 修改分区架构, 增加用以接受新分区的文件组   
ALTER PARTITION SCHEME PS_HistoryArchive   
NEXT USED [PRIMARY]   
    
-- 5. 修改分区函数, 增加分区用以接受新数据   
SET @dt = '20040101'  
ALTER PARTITION FUNCTION PF_HistoryArchive()   
SPLIT RANGE(@dt)  

#16

OK 打完收工

#17

引用 9 楼 feixianxxx 的回复:

引用 3 楼 fredrickhu 的回复:
引用 1 楼 feixianxxx 的回复:
sf不留


懒得理你

小气 。。。

小P哥每次不留沙发

#18

UP
我需要通俗的说法 讲讲分区表干吗使的

#19

关注中

#20

分区表可以自学，相信楼主

#21

引用 18 楼 feixianxxx 的回复:

UP
我需要通俗的说法 讲讲分区表干吗使的

简单点哈， 

一个西瓜很重，一个人拿太重，而且一个权利太集中似乎不太好，怎么办？ 那切要两半，左边一半，右边一半，分别编上号，左1号，右1号，方便拿，责任分明，哪半少找哪个拿的!

可是就两个人拿，其它人不干 了，凭什么就他们俩拿，我们也要，那就左1号再分成两半，右1号再分两半，分别编号为 左11号左12号，右11号右12号，责任还是分明的，查找某个瓜子也成为现实

。。
如何下去，分到合适的大小


简单总结：

一个西瓜分两半
左一半
右一半
分区表玩的好不好
就看你太极打的好不好!

#22

当然 这瓜如何切，要看瓜的形状、大小、瓜子的分页情况，以及当事的人心跳频率 等综合因素

#23

引用 20 楼 haiwer 的回复:

分区表可以自学，相信楼主

海爷先指点12 

#24

引用 21 楼 happyflystone 的回复:

引用 18 楼 feixianxxx 的回复:
UP
我需要通俗的说法 讲讲分区表干吗使的


简单点哈，

一个西瓜很重，一个人拿太重，而且一个权利太集中似乎不太好，怎么办？ 那切要两半，左边一半，右边一半，分别编上号，左1号，右1号，方便拿，责任分明，哪半少找哪个拿的!

可是就两个人拿，其它人不干 了，凭什么就他们俩拿，我们也要，那就左1号再分成两半，右1号再分两半，分别编号为 左11号左12号，右11号右12号，责任还是分明的，查找某个瓜子也成为现实

。。
如何下去，分到合适的大小


简单总结：

一个西瓜分两半
左一半
右一半
分区表玩的好不好
就看你太极打的好不好!

哈哈  经典

#25

引用 21 楼 happyflystone 的回复:

引用 18 楼 feixianxxx 的回复:
UP
我需要通俗的说法 讲讲分区表干吗使的


简单点哈，

一个西瓜很重，一个人拿太重，而且一个权利太集中似乎不太好，怎么办？ 那切要两半，左边一半，右边一半，分别编上号，左1号，右1号，方便拿，责任分明，哪半少找哪个拿的!

可是就两个人拿，其它人不干 了，凭什么就他们俩拿，我们也要，那就左1号再分成两半，右1号再分两半，分别编号为 左11号左12号，右11号右12号，责任还是分明的，查找某个瓜子也成为现实

。。
如何下去，分到合适的大小


简单总结：

一个西瓜分两半
左一半
右一半
分区表玩的好不好
就看你太极打的好不好!

石头哥 回头我先一边吃西瓜 一边打太极 估计分区表就会了 

#26

石头哥 回头我先一边吃西瓜 一边打太极 估计分区表就会了 

---
损我呀？
我说的实话啊

#27

引用 24 楼 fredrickhu 的回复:

引用 21 楼 happyflystone 的回复:
引用 18 楼 feixianxxx 的回复:
UP
我需要通俗的说法 讲讲分区表干吗使的


简单点哈，

一个西瓜很重，一个人拿太重，而且一个权利太集中似乎不太好，怎么办？ 那切要两半，左边一半，右边一半，分别编上号，左1号，右1号，方便拿，责任分明，哪半少找哪个拿的!

可是就两个人拿，其它人不干 了，凭什么就他们俩拿，我们也要，那就左1号再分成两半，右1号再分两半，分别编号为 左11号左12号，右11号右12号，责任还是分明的，查找某个瓜子也成为现实

。。
如何下去，分到合适的大小


简单总结：

一个西瓜分两半
左一半
右一半
分区表玩的好不好
就看你太极打的好不好!


哈哈  经典

枪哥的例子经典呀,学习

#28

引用 26 楼 happyflystone 的回复:

石头哥 回头我先一边吃西瓜 一边打太极 估计分区表就会了

---
损我呀？
我说的实话啊

没 开玩笑呀 呵呵  怎么会损你。。。

感谢来不及

很生动形象 呵呵 

#29

好吧我也承认我不懂分区表

#30

这么说吧，一个系统使用比较长时间后，往往会出现以下问题：
1、某些表单表数据太多，查询慢，加索引耶解决不了问题
2、数据多的表使用的时候经常只是用很少一部分，比如大部分情况只查询当月数据。
这时很自然的就会想到分表，历史表和当前表，但是很麻烦，比如
1、需要过一段时间做一次倒数据操作
2、分表后万一需要查询整体数据的时候将变得很难实现。
然后开始有了分区的概念，已开始是分区视图，将不同服务器的按各种方式分区的表组成一个跨服务器的分区视图，试图发挥多服务器的性能平衡，来实现可以快速查询小范围数据和可以查询整体数据这两个矛盾的需求。但是链接服务器固有的问题和低性能以及很难设置和使用使得跨服务器的分区视图没有多少市场。
到了2005，开始有了分区表的概念，把分区思想放在一个表里实现，对外是一个表，内部存储是分区的，索引也可以分区。

#31

引用 30 楼 haiwer 的回复:

这么说吧，一个系统使用比较长时间后，往往会出现以下问题：
1、某些表单表数据太多，查询慢，加索引耶解决不了问题
2、数据多的表使用的时候经常只是用很少一部分，比如大部分情况只查询当月数据。
这时很自然的就会想到分表，历史表和当前表，但是很麻烦，比如
1、需要过一段时间做一次倒数据操作
2、分表后万一需要查询整体数据的时候将变得很难实现。
然后开始有了分区的概念，已开始是分区视图，将不同服务器的按各种方式分区的表组成一个跨服务器的分区视图，试图发挥多服务器的性能平衡，来实现可以快速查询小范围数据和可以查询整体数据这两个矛盾的需求。但是链接服务器固有的问题和低性能以及很难设置和使用使得跨服务器的分区视图没有多少市场。
到了2005，开始有了分区表的概念，把分区思想放在一个表里实现，对外是一个表，内部存储是分区的，索引也可以分区。

我理解下 

#32

你可以理解一个分区表内有多个表，如果你的查询能命中一个分区，那就相当于你查询一个小表。
如果你的查询需要查询多个分区，那这个表还是一个整体，你不必分多次查询。

#33

引用 32 楼 haiwer 的回复:

你可以理解一个分区表内有多个表，如果你的查询能命中一个分区，那就相当于你查询一个小表。
如果你的查询需要查询多个分区，那这个表还是一个整体，你不必分多次查询。

那一个分区表说到底还是一个表吧 ？
他在内存存在的时候分一块块还是一大块阿？

#34

那一个分区表说到底还是一个表吧 ？ 
他在内存存在的时候分一块块还是一大块阿？



---

是的
分区的，你可以通过文件文件组分到不同逻辑盘或物理盘

#35

引用 34 楼 happyflystone 的回复:

那一个分区表说到底还是一个表吧 ？
他在内存存在的时候分一块块还是一大块阿？


---

是的
分区的，你可以通过文件文件组分到不同逻辑盘或物理盘

那分区表的内存分配呢

#36

引用 7 楼 soft_wsx 的回复:

不敢搬门弄斧

不敢关公面前耍大刀

#37

分区表和内存分配无关.
简单的说,分区表就是将海量数据按照分区函数的分区规则均匀分布到不同的物理磁盘.通常是磁盘阵列,这样在读写的时候获得更好的I/O性能.所以说,一般情况下通过分区表+磁盘阵列的方式来实现获得性能上的提升.
对于海量数据的分割,通常为垂直分割和水平分割,垂直分割为分表,也就是比如说一个表有60个栏位,可以分成两个30个栏位的表来存储.而水平分割就是所谓的分区表了,将数据分成不同的区,然后读取.
读写的时候,根据分区函数的分区规则,读写器会首先定位分区函数然后在定位到行。

#38

引用 37 楼 hery2002 的回复:

分区表和内存分配无关.
简单的说,分区表就是将海量数据按照分区函数的分区规则均匀分布到不同的物理磁盘.通常是磁盘阵列,这样在读写的时候获得更好的I/O性能.所以说,一般情况下通过分区表+磁盘阵列的方式来实现获得性能上的提升.
对于海量数据的分割,通常为垂直分割和水平分割,垂直分割为分表,也就是比如说一个表有60个栏位,可以分成两个30个栏位的表来存储.而水平分割就是所谓的分区表了,将数据分成不同的区,然后读取.
读写的时候,根据分区函数的分区规则,读写器会首先定位分区函数然后在定位到行。

一个表的数据就这么分配到不同物理磁盘  会不会发生丢失  一开始数据可是在一个物理磁盘里

#39

引用 38 楼 feixianxxx 的回复:

一个表的数据就这么分配到不同物理磁盘  会不会发生丢失  一开始数据可是在一个物理磁盘里

...

#40

想高手学习....

#41

真够深奥的，我都看不懂！

#42

引用 38 楼 feixianxxx 的回复:

引用 37 楼 hery2002 的回复:
分区表和内存分配无关.
简单的说,分区表就是将海量数据按照分区函数的分区规则均匀分布到不同的物理磁盘.通常是磁盘阵列,这样在读写的时候获得更好的I/O性能.所以说,一般情况下通过分区表+磁盘阵列的方式来实现获得性能上的提升.
对于海量数据的分割,通常为垂直分割和水平分割,垂直分割为分表,也就是比如说一个表有60个栏位,可以分成两个30个栏位的表来存储.而水平分割就是所谓的分区表了,将数据分成不同的区,然后读取.
读写的时候,根据分区函数的分区规则,读写器会首先定位分区函数然后在定位到行。

一个表的数据就这么分配到不同物理磁盘  会不会发生丢失  一开始数据可是在一个物理磁盘里

这个不用担心，非分区表也可以把一个表的数据放在不同的物理磁盘里，一个文件组可以包含不同物理磁盘的文件，看起来一个表的数据放在一个文件组，但实际可能放在不同磁盘，这个2000已经实现
如果把表数据、索引看作一个表的数据，那早在200以前版本就可以把一个表的数据和索引分放不同磁盘

#43

引用 42 楼 haiwer 的回复:

引用 38 楼 feixianxxx 的回复:
引用 37 楼 hery2002 的回复:
分区表和内存分配无关.
简单的说,分区表就是将海量数据按照分区函数的分区规则均匀分布到不同的物理磁盘.通常是磁盘阵列,这样在读写的时候获得更好的I/O性能.所以说,一般情况下通过分区表+磁盘阵列的方式来实现获得性能上的提升.
对于海量数据的分割,通常为垂直分割和水平分割,垂直分割为分表,也就是比如说一个表有60个栏位,可以分成两个30个栏位的表来存储.而水平分割就是所谓的分区表了,将数据分成不同的区,然后读取.
读写的时候,根据分区函数的分区规则,读写器会首先定位分区函数然后在定位到行。

一个表的数据就这么分配到不同物理磁盘  会不会发生丢失  一开始数据可是在一个物理磁盘里

这个不用担心，非分区表也可以把一个表的数据放在不同的物理磁盘里，一个文件组可以包含不同物理磁盘的文件，看起来一个表的数据放在一个文件组，但实际可能放在不同磁盘，这个2000已经实现
如果把表数据、索引看作一个表的数据，那早在200以前版本就可以把一个表的数据和索引分放不同磁盘

感觉还是怪怪的 呵呵 
一个表的东西放不同盘里 

#44

认真看了海爷和和尚哥的回复，有这样两想法不知道对不对：
1.对分区存放的数据可以进行选择，如日期大于某一天记录的放到这个磁盘，不知道能否实现这样的功能。
2.方便地进行数据恢复。因为不同的行存储在不同的磁盘，所以一个磁盘损坏了不会影响其它磁盘上的记录，可以最大程度地避免数据丢失。
不知道我的想法是不是分区表所能实现的功能

#45

我的理解是，一个表装在硬盘上，
拿起菜刀，一劈二，是为分区。

#46

分磁盘并不是分区表特有的，分区表是可以指定各个分区存放的文件组，但是可以多个分区放在同一个文件组。

分区表可以方便地进行分区的合并和分区的拆分，这是在很多应用中很有用的，但是要求合并的分区原来放在同一个文件组。

#47

每天UP

#48

不懂，学习一下

#49

看了很有帮助额