22,210
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
zzh1983sg 2010-07-20
- 打赏
- 举报
xie
hzvcan 2010-02-02
- 打赏
- 举报
-------------------------------------------------------------------
--分区表的使用 sql2005
--大表的形式获得小表的效率,普通表需要全表扫描，分区表由系统确定对应的分区，只扫描该分区
--避免了全表扫描
--更详细的信息参考msdn(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/udb9/html/cc5bf181-18a0-44d5-8bd7-8060d227c927.htm)
-------------------------------------------------------------------
create database test--建立多文件组的数据库
on primary
(name=p,
filename='f:\test\p.mdf'
),
filegroup g1
(name=g1,
filename='f:\test\g1.ndf'
),
filegroup g2
(name=g2,
filename='f:\test\g2.ndf'
)
log on
(name=log,
filename='f:\test\log.ldf'
)
create partition function fun(datetime)--分区函数
as range left for values('2010-1-1','2012-1-1')
create partition scheme s--分区方案
as partition fun to ([primary],g1,g2)
--创建表,应用分区方案s
create table tb(id int identity(1,1),name varchar(100),age int,birthday datetime) on s(birthday)
--填充数据,耗时 00:20:31 记录数datediff(mi,'2008-1-1','2014-1-1')=3156480
declare @name varchar(100)
declare @age int,@birthday datetime
set @birthday='2008-1-1'
while @birthday<'2014-1-1'
begin
set @age=floor(rand()*100)
set @name=replicate(char(@age),@age)
set @birthday=dateadd(mi,1,@birthday)
insert into tb(name,age,birthday) values(@name,@age,@birthday)
end
create table tb1(id int identity(1,1),name varchar(100),age int,birthday datetime)--创建普通表
set identity_insert tb1 on
insert into tb1(id,name,age,birthday) select * from tb--把分区表的数据都导入到普通表
--------------------------------------------------------------------------------
--修改分区
--------------------------------------------------------------------------------
--新增一个分区,在新增分区之前要有一个标记为next used 的文件组，可以新建一个文件组进行标记，也可以是
--已经存在的文件组进行标记
alter database test--新增文件组
add filegroup g3
alter database test--新增一个文件到文件组g3
add file
(
name='g3',
filename='f:\test\g3.ndf'
) to filegroup g3
alter partition scheme s next used g3--将文件组g3标记为next used,如果指定了 filegroup_name 且已经存在具有 NEXT USED 属性的文件组，则 NEXT USED 属性从现有的文件组转移到 filegroup_name。
alter partition function fun()--新增一个分区，边界值所属的一边为新分区。分区函数中指定边界值属于left,所以1900-1-1到2009-1-1为新分区
split range('2009-1-1')--
--合并分区
--原来分区情况2009-1-1以前(g3),2009-1-2——2010-1-1(primary),2010-1-2——2012-1-1(g1),2012-1-2以后(g2)
--g3 有next used标志
--合并之后g2变大，说明边界值所在分区为要转移的分区(删除一个分区并将该分区中存在的所有值都合并到剩余的某个分区中。RANGE (boundary_value) 必须是一个现有边界值，已删除分区中的值将合并到该值中。如果最初保存 boundary_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组。合并的分区驻留在最初不保存 boundary_value 的文件组中)
alter partition function fun()
merge range('2012-1-1')
--收缩数据库后，g1变为原始大小
backup log test with no_log
go
dbcc shrinkdatabase(test,0)
select * from sys.partitions where object_id=object_id('tb')--查看分区,此时有三个分区
--??????????????????????????????????????????????
--如果最初保存 boundary_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组
--以下实验用来验证上面这句话
--1.将g1标记为next used(从g3转移到g1)
alter partition scheme s next used g1
--select * from sys.partitions where object_id=object_id('tb')--查看分区,此时有三个分区
--2.新增一个分区,g1变大
alter partition function fun()
split range('2012-1-1')
--select * from sys.partitions where object_id=object_id('tb')--查看分区,此时有四个分区
--3.合并分区
alter partition function fun()
merge range('2012-1-1')
--select * from sys.partitions where object_id=object_id('tb')--查看分区,此时有三个分区
丰云 2010-02-02
- 打赏
- 举报
xuexi
feixianxxx 2010-02-01
- 打赏
- 举报
不知道
没实践过
没实践过
ws_hgo 2010-02-01
- 打赏
- 举报
MS SQL Server：分区表、分区索引
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 ]
}
[ ; ]
yananguo_1985 2010-02-01
- 打赏
- 举报
看看这个吧。
http://hi.baidu.com/psc2006hedan/blog/item/2e6d5c0f4f1456206159f37d.html
http://hi.baidu.com/psc2006hedan/blog/item/2e6d5c0f4f1456206159f37d.html
--小F-- 2010-02-01
- 打赏
- 举报
SQL Server 2005 分区表实践——建立分区表（partition table）
问题：有一个订单表 Orders，要转换成分区表，以订单日期 OrderDate 为分区列，目前含有订单日期为 1996-07-04 ~ 1998-05-06 的数据。可以在 SQL Server 2000 Northwind 数据库中找到 Orders 表，下面是简化了的表结构：
create table dbo.Orders
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
,constraint PK_Orders primary key noclustered (OrderID, CustomerID)
)
go
create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
go
1. 创建分区函数（partition function）
在创建分区函数的时候，我一般这样命名分区函数：'PF' + Table Name + 分区字段名 + 'Range'，例如：'PF_Orders_OrderDateRange'，如果字段名较长的话，则可以省略去，可以这样命名： 'PF_Orders_DateRange'
create partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
go
分区函数 'PF_Orders_OrderDateRange' 有 3 个边界值（'1997-01-01', '1998-01-01', '1999-01-01'），这三个边界值（boundary value）组成了 4 个分区（partition），并且 “range right” 表明边界值属于右边的分区。下面使用存储过程 dbo.sp_show_partition_range 来查看分区范围：
exec dbo.sp_show_partition_range @partition_function = 'PF_Orders_OrderDateRange'
partition_function partition minval value maxval
------------------------- ----------- ------------------------- -------- ------------------------
PF_Orders_OrderDateRange 1 NULL <= val < 1997-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 4 1999-01-01 00:00:00.000 <= val < NULL
每个分区的最大值和最小值，一清二楚。获得 dbo.sp_show_partition_range 代码。
2. 创建分区方案（partition scheme）
分区方案定义了，分区表或者分区索引的每个分区的数据存放在哪个文件组上。试想，如果没有分区方案的话，如何实现把分区表中不同的分区存放在不同的文件组上？我们可以看下创建普通数据表的语法：
create table [table name] ... on [filegroup]
显然，普通表整个表的数据，只能存放在同一个文件组上。为了实现分区机制，才引入了分区方案这个概念。每个分区表只属于一个方案（scheme），因此分区方案可以这样命名：'PS' + Table Name
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary], [primary], [primary])
go
分区方案 PS_Orders 规定了，把分区表的 4 个分区分别存放在主文件组 primary 上。分区方案中指定的文件组数目，不能少于分区函数中划定的分区数目；但可以多于分区函数中划定的分区数目。多出的第 1 个文件组用来指定当分区表增加分区时，下一个分区所使用的文件组；多出的其他文件组将被忽略。
下面的例子，分区方案指定了 5 个文件组（多出了 1 个文件组）。
drop partition scheme PS_Orders
go
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary],[primary], [primary],[primary])
go
Partition scheme 'PS_Orders' has been created successfully.
'PRIMARY' is marked as the next used filegroup in partition scheme 'PS_Orders'.
下面的例子，分区方案指定了 7 个文件组（多出了 3 个文件组）。
drop partition scheme PS_Orders
go
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary],[primary], [primary],[primary],[primary],[primary])
go
Partition scheme 'PS_Orders' has been created successfully.
'PRIMARY' is marked as the next used filegroup in partition scheme 'PS_Orders'.
2 filegroup specified after the next used filegroup are ignored.
如果分区表所有的分区都将分配在同一个文件组 [primary] 上，那么可以使用下面更简洁的方法：
drop partition scheme PS_Orders
go
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
all to ([primary])
go
3. 创建分区表（partition table）
创建了分区函数和分区方案，准备工作做完了，现在终于可以开始创建分区表了。创建分区表和普通表的语法大致相同，不同之处：普通表需要指定所存放的文件组，分区表需要指定分区方案。
create table dbo.Orders
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
)
on PS_Orders(OrderDate)
go
根据订单表 Orders 查询时经常使用 OrderDate 范围条件来查询的特点，我们最好在 Orders.OrderDate 列上建立聚集索引（clustered index）。为了便于进行分区切换（partition swtich），大多数情况下，建议在分区表上建立分区索引。下面建立聚集分区索引：
create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
on PS_Orders(OrderDate)
如果没有指定 “on PS_Orders(OrderDate)”，默认建立的聚集索引和分区表的分区方案相同。
另外 Orders 分区表需要在（OrderID, CustomerID）上建立主键。我们知道主键实际上是个唯一索引，但分区表在建立唯一索引（分区索引）的时候，分区列必须是唯一索引的一部分。为什么要这样子呢？因为 SQL Server 不但要保证索引在各个分区是唯一的，还要保证在整个表中是唯一的。
alter table dbo.Orders add constraint PK_Orders primary key (OrderID, CustomerID, OrderDate)
如果不指定 OrderDate 的话，则会出现错误：1908
Msg 1908, Level 16, State 1, Line 1
Column 'OrderDate' is partitioning column of the index 'PK_Orders'.
Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
查看分区表 Orders 上的索引：
exec sp_helpindex 'dbo.Orders'
index_name index_description index_keys
--------------------- ------------------------------------------------------ ------------------------------
IXC_Orders_OrderDate clustered located on PS_Orders OrderDate
PK_Orders nonclustered, unique, primary key located on PS_Orders OrderID, CustomerID, OrderDate
4. 向分区表中填充数据
insert into dbo.Orders
select OrderID, CustomerID, EmployeeID, OrderDate
from dbo.Orders_From_SQL2000_Northwind
数据表 dbo.Orders_From_SQL2000_Northwind，是从 SQL Server 2000 中 Northwind.Orders 迁移过来的。
5. 查看分区表各分区数据情况（数据行数，最大最小 OrderDate 值）
select partition = $partition.PF_Orders_OrderDateRange(OrderDate)
,rows = count(*)
,minval = min(OrderDate)
,maxval = max(OrderDate)
from dbo.Orders
group by $partition.PF_Orders_OrderDateRange(OrderDate)
order by partition
partition rows minval maxval
----------- ----------- ----------------------- -----------------------
1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
2 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
3 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
(3 row(s) affected)
从以上结果集中可以看出：分区表 Orders 的 3 个分区中已经填入了数据。这里要注意 $partition.partition_function_name(expression) 这个函数，或许像我一样：一辈子没见过这样的函数：）
本文由 www.sqlstudy.com 原创，版权所有，转载请注明作者和出处！
本文链接：http://www.sqlstudy.com/sql_article.php?id=2008071101
SQL Server数据库表分区操作过程由三个步骤组成：
1. 创建分区函数
2. 创建分区架构
3. 对表进行分区
下面将对每个步骤进行详细介绍。
步骤一：创建一个分区函数
此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值(how)。这个操作并不涉及任何表格，只是单纯的定义了一项技术来分割数据。
我们可以通过指定每个分区的边界条件来定义分区。例如，假定我们有一份Customers表，其中包含了关于所有客户的信息，以一一对应的客户编号(从1到1,000,000)来区分。我们将通过以下的分区函数把这个表分为四个大小相同的分区：
CREATEPARTITIONFUNCTIONcustomer_partfunc(int)
ASRANGERIGHT
FORVALUES(250000,500000,750000)
这些边界值定义了四个分区。第一个分区包括所有值小于250,000的数据，第二个分区包括值在250,000到49,999之间的数据。第三个分区包括值在500,000到7499,999之间的数据。所有值大于或等于750,000的数据被归入第四个分区。
请注意，这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的，如果使用"RANGE LEFT"语句，则上述第一个分区应该包括所有值小于或等于250,000的数据，第二个分区的数据值在250,001到500,000之间，以此类推。
步骤二：创建一个分区架构
一旦给出描述如何分割数据的分区函数，接着就要创建一个分区架构，用来定义分区位置(where)。创建过程非常直截了当，只要将分区连接到指定的文件组就行了。例如，如果有四个文件组，组名从"fg1"到"fg4"，那么以下的分区架构就能达到想要的效果：
CREATEPARTITIONSCHEMEcustomer_partscheme
ASPARTITIONcustomer_partfunc
TO(fg1,fg2,fg3,fg4)
注意，这里将一个分区函数连接到了该分区架构，但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表，我们都可以使用该分区架构(或仅仅是分区函数)。
步骤三：对一个表进行分区
定义好一个分区架构后，就可以着手创建一个分区表了。这是整个分区操作过程中最简单的一个步骤。只需要在表创建指令中添加一个"ON"语句，用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数，所以不需要再指定分区函数了。
例如，使用以上的分区架构创建一个客户表，可以调用以下的Transact-SQL指令：
CREATETABLEcustomers(FirstNamenvarchar(40),LastNamenvarchar(40),CustomerNumberint)
ONcustomer_partscheme(CustomerNumber)yananguo_1985 2010-02-01
- 打赏
- 举报
1、SQL 2005 提供了在数据库的文件组之间表分区的功能，水平分区允许把表按分区scheme分为多个小的组。表分区用于非常大的，从几百GB到TB、甚至更大数据库。
2、通过SQL 2005 中的分区表，可以对表进行设计（使用函数和架构），从而将具有相同分区键的所有行都直接放置到（且总是转到）特定的位置。函数用于定义分区边界以及放置第一个值的分区。在使用LEFT 分区函数时，第一个值将作为第一个分区中的上边界。在使用RIGHT 分区函数时，第一个值将作为第二个分区的下边界。定义函数后即可创建分区架构，分区架构可以将对象映射到一个或多个文件组。为了确定数据的相应物理位置，分区架构将使用了分区函数。根据分区架构创建表。
3、实现分区表的步骤：
确定是否应为对象分区
确定分区键和分区数目
确定是否应使用多个文件组
创建文件组
为分区创建分区函数 Create the partition function
创建分区架构 Create the partition scheme
创建分区表 Create the partitioned table
use adventureWorks
go
create partition function emailPF(nvarchar(50)) as range right for values ('G','N')
go
create partition scheme emailPS as partition emailPF to (fg1,fg2,fg3)
go
create table customermail (custid int, email nvarchar(50)) on emailPS(email)
go
insert customermail values (1, 'ab@test.com.cn')
insert customermail values (2, 'K1@test.com.cn')
insert customermail values (3, 'z1@test.com.cn')
insert customermail values (4, 'g2@test.com.cn')
insert customermail values (5, 'a2@test.com.cn')
insert customermail values (6, 'TT@test.com.cn')
select * from customermail
