有关MSSQL2005分区表几点困惑；

[dolphin2001]

最近参与的一个系统由于数据量比较大，大概是8000万左右，根据在网上查找的资料。了解到MSSQL2005的分区表可以将一个表的数据分散存储到不同的分区上，每一个分区可以指定到一个文件组上，这正好可以帮助解决在某一个文件组上单表数据文件过大的问题；于是按照网上的一些资料，进行了一下测试，结果是：执行SQL预计进行查询，可以查到对应表的数据已经按照分区函数进行了分区，但是表的数据并没有按照我指定的分区架构中指定的文件组进行分开保存，而是都是存放在一个PRIMARY的文件组的文件中；请教大家，是不是有哪里做的不对。下面的测试过程中的SQL脚本，希望大家能帮忙指点：

--创建分区函数
CREATE PARTITION FUNCTION [p_KKKSN](int) AS RANGE LEFT FOR VALUES
(150000, 190000)

--给数据库增加文件组，用来存放分区表的数据。
ALTER DATABASE abc ADD FILEGROUP T1
GO
ALTER DATABASE abc
ADD FILE
(
NAME = N'T1',FileName = 'D:\DBDATA\T1.mdf'
)
TO FILEGROUP T1
GO

ALTER DATABASE abc ADD FILEGROUP T2
GO
ALTER DATABASE abc
ADD FILE
(
NAME = N'T2',FileName = 'D:\DBDATA\T2.mdf'
)
TO FILEGROUP T2
GO

ALTER DATABASE abc ADD FILEGROUP T3
GO
ALTER DATABASE abc
ADD FILE
(
NAME = N'T3',FileName = 'D:\DBDATA\T3.mdf'
)
TO FILE



--创建分区架构
CREATE PARTITION SCHEME KKKSNPScheme
AS
PARTITION p_KKKSN

--按照分区函数所划分的范围，一一与文件组进行对应，默认情况下对应的[PRIMARY]文件组
TO T1, T2, T3)
GO

--创建数据表
CREATE TABLE [dbo].[TB_KKKs](
[FUniqueID] [int] identity NOT NULL,
[FKKKSN] [int] NOT NULL,
[FKKKCode] [varchar](30) NULL,
CONSTRAINT [PK_TB_KKKs] PRIMARY KEY CLUSTERED
(
[FUniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON KKKSNPScheme (FKKKSN)
GO

--按照分区函数的规则，插入一些测试的数据，比较简单，在这里省略。。。

--测试数据插入之后，执行SQL语句查询分区后的结果：
select $partition.p_KKKSN(FKKKSN) ,count(*) from TB_KKKs
group by $partition.p_KKKSN(FKKKSN)

[Andy-W]

Use test
go
If Object_id('Duty') Is Not null
    Drop Table Duty
Go
If Object_id('DutyHistory') Is Not null
    Drop Table DutyHistory
Go

If Exists(Select 1 From sys.partition_schemes Where name='MyPS1')
    Drop Partition scheme MyPS1
Go
If Exists(Select 1 From sys.partition_functions Where name='MyPF1')
    Drop Partition Function MyPF1
Go

--建立分区函数
Create Partition Function MyPF1(datetime)
As Range Left
For Values('2007/12/31')
Go
--建立分区方案

Create Partition Scheme MyPS1
As Partition MyPF1
To ([Primary],[Primary])
Go
/*
1，建立分区函数，分区方案是有先后顺序的。
2，分区函数提供的值的数目n，不能超过 999。所创建的分区数等于 n + 1
*/
Go
--建立测试表
Create Table Duty(
        id int Identity(1,1) Not Null,
        Name nvarchar(50),
        WorkDate datetime,
        WorkHours numeric(5,1),
        Constraint PK_Duty Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)
Go
Create Table DutyHistory(
        id int Identity(1,1) Not Null,
        Name nvarchar(50),
        WorkDate datetime,
        WorkHours numeric(5,1),
        Constraint PK_DutyHistory Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)

--这里就是用到了表分区方案。
Go
--插入2008/1一个月的数据
Declare
        @MaxRows int,
        @StartDate datetime

Select
    @MaxRows=31,
    @StartDate='2007/12/31'
   
;With
a0 as(Select id=1 Union All Select id=1),
a1 as(Select id=a.id From a0 a,a0 b),
a2 as(Select id=a.id From a1 a,a1 b),
a3 as(Select id=a.id From a2 a,a2 b),
a4 as(Select id=a.id From a3 a,a3 b),
a5 as(Select id=a.id From a4 a,a4 b),
Row as(Select id=Row_number() Over(Order By id) From a5)
Insert Into Duty
    ([name],WorkDate,WorkHours)
Select 'Robert',@StartDate+id,Rand()*6 From Row Where id <=@MaxRows
/*
--debug 查询两表的分区情况
Select * from sys.partitions
Where object_id In(Select object_id From sys.tables Where name In('Duty','DutyHistory'))
*/
Go
--切换分区前
Select * From Duty
Select * From DutyHistory
--切换分区 Duty -->DutyHistory
Alter Table Duty Switch Partition 2 To DutyHistory Partition 2
--切换分区后
Select * From Duty
Select * From DutyHistory

Go
--修改分区函数,分区方案，方便下个月切换分区

--1.把>2007/12/31的分区拆分为两个分区
Alter Partition Scheme MyPS1
Next Used [Primary]
Go
Alter Partition Function MyPF1()
Split Range('2008/1/31')
Go
--2.把 <2008/1/31的两个分区合并
Alter Partition Function MyPF1()
Merge Range('2007/12/31')
Go
--插入2月份数据
Declare
        @MaxRows int,
        @StartDate datetime

Select
    @MaxRows=29,
    @StartDate='2008/1/31'
   
;With
a0 as(Select id=1 Union All Select id=1),
a1 as(Select id=a.id From a0 a,a0 b),
a2 as(Select id=a.id From a1 a,a1 b),
a3 as(Select id=a.id From a2 a,a2 b),
a4 as(Select id=a.id From a3 a,a3 b),
a5 as(Select id=a.id From a4 a,a4 b),
Row as(Select id=Row_number() Over(Order By id) From a5)
Insert Into Duty
    ([name],WorkDate,WorkHours)
Select 'Robert',@StartDate+id,Rand()*6 From Row Where id <=@MaxRows

Go
--切换分区前
Select * From Duty
Select * From DutyHistory
--切换分区 Duty -->DutyHistory
Alter Table Duty Switch Partition 2 To DutyHistory Partition 2
--切换分区后
Select * From Duty
Select * From DutyHistory

my blog:
http://www.cnblogs.com/wghao/archive/2008/05/28/1208813.html

[dolphin2001]

谢谢你的回复，我的疑问是：分区表不是根据分区架构定义的存储方案将一个表的数据分散保存到多个文件组里面的吗？

[Andy-W]

对。




这里：

CREATE PARTITION FUNCTION [p_KKKSN](int) AS RANGE LEFT FOR VALUES
(150000, 190000) 

--创建分区架构
CREATE PARTITION SCHEME KKKSNPScheme
AS
PARTITION p_KKKSN

--按照分区函数所划分的范围，一一与文件组进行对应，默认情况下对应的[PRIMARY]文件组
TO T1, T2, T3)
GO 

就是，创建一个将已分区表或已分区索引的分区映射到文件组的方案。

>1 And <=150000 保持在分区T1
>150000 And <=190000 保持在分区T2
>190000 保持在分区T3

[Andy-W]

纠正一下：
<=150000 保持在分区T1

[dolphin2001]

按理说，建立分区架构之后，

符合 <=150000 的数据保存在分区T1
>150000 And <=190000 保存在分区T2
>190000 保存在分区T3

[dolphin2001]

因为我建立了3个文件组（T1,T2,T3)对应了以上三个范围的数据，但是我插入测试数据之后，通过观察数据文件的大小，插入的数据没有存放到 T1、T2、T3对应的数据文件中，而是都存放在 PRIMARY 的数据文件中，所以感到很困惑。。。。。

[Andy-W]

--创建数据表
CREATE TABLE [dbo].[TB_KKKs](
[FUniqueID] [int] identity NOT NULL,
[FKKKSN] [int] NOT NULL,
[FKKKCode] [varchar](30) NULL,
CONSTRAINT [PK_TB_KKKs] PRIMARY KEY CLUSTERED
(
[FUniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON KKKSNPScheme (FKKKSN)
GO

去掉中的ON [PRIMARY]，再测试下。

[rucypli]

ON {filegroup | DEFAULT}

指定存储表的文件组。如果指定 filegroup，则表将存储在指定的文件组中。数据库中必须存在该文件组。如果指定 DEFAULT，或者根本未指定 ON 参数，则表存储在默认文件组中。

ON {filegroup | DEFAULT} 也可以在 PRIMARY KEY 约束或 UNIQUE 约束中指定。这些约束会创建索引。如果指定 filegroup，则索引将存储在指定的文件组中。如果指定 DEFAULT，则索引将存储在默认文件组中。如果约束中没有指定文件组，则索引将与表存储在同一文件组中。如果 PRIMARY KEY 约束或 UNIQUE 约束创建聚集索引，则表的数据页将与索引存储在同一文件组中。

[dolphin2001]

[Quote=引用 7 楼 DVD_01 的回复:]
--创建数据表
CREATE TABLE [dbo].[TB_KKKs](
[FUniqueID] [int] identity NOT NULL,
[FKKKSN] [int] NOT NULL,
[FKKKCode] [varchar](30) NULL,
CONSTRAINT [PK_TB_KKKs] PRIMARY KEY CLUSTERED
(
[FUniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON KKKSNPScheme (FKKKSN)
GO

去掉中…
[/Quote]

去掉之后，执行会报错。。。

消息 1908，级别 16，状态 1，第 1 行
列 'FKKKSN' 是索引 'PK_TB_KKKs' 的分区依据列。唯一索引的分区依据列必须是索引键的子集。
消息 1750，级别 16，状态 0，第 1 行
无法创建约束。请参阅前面的错误消息。

[dolphin2001]

如果是将创建表的SQL语句修改为：

CREATE TABLE [dbo].[TB_KKKs](
[FUniqueID] [int] identity NOT NULL,
[FKKKSN] [int] NOT NULL,
[FKKKCode] [varchar](30) NULL
) ON KKKSNPScheme (FKKKSN)

就会将数据按照分区架构定义的分区方案进行分散存储到不同的数据文件中了；

如果是这样的话，难道不能创建聚族索引？？？

[拓狼]

这里有详细的介绍
http://www.windbi.com/showtopic-832.aspx

[dolphin2001]

谢谢大家的帮助；