创建sql server 2005 分区表实例

创建sql server 2005 分区表实例
sql server 2005 提供了分区表，这使得我们在处理超大容量数据库性能方面有了新的选择，通过建立分区表，可以把数据存储在不同的物理硬盘上，在多cpu及多硬盘的服务器上，有效的利用了i/o并行处理，使得不至于存取数据时卡死在某一个物理硬盘上。

建立分区表，主要有三个步骤：
1、创建分区函数以指定如何分区使用该函数的表或索引。

2、创建分区方案以指定分区函数的分区在文件组上的位置。

3、创建使用分区方案的表或索引。

现在以一个实例，来说明分区别的建立方法：有一个表，主键是id int型，数据量为100W(模拟为这么多，实际上过500W或数据量不大但表压力巨大时用分区表才更有意义)现在把该表划分在不同的硬盘上，以提高i/o的并行处理能力，提升性能，本例每隔30W划分为一个区。

一、准备工作
--建数据库
Create DATABASE Tmp_Db
ON
(NAME= Tmp_Db_dat,
FILENAME='D:\Data\Tmp_Db.mdf',
SIZE= 10,
MAXSIZE= 50,
FILEGROWTH= 5 )
LOG ON
(NAME= Tmp_Db_log,
FILENAME='D:\Data\Tmp_Db.ldf',
SIZE= 5MB,
MAXSIZE= 25MB,
FILEGROWTH= 5MB )
GO
USE Tmp_Db
GO
--增加文件组这里我们暂增加个
Alter DATABASE Tmp_Db ADD FILEGROUP [FILEGROUP_1]
Alter DATABASE Tmp_Db ADD FILEGROUP [FILEGROUP_2]
Alter DATABASE Tmp_Db ADD FILEGROUP [FILEGROUP_3]
Alter DATABASE Tmp_Db ADD FILEGROUP [FILEGROUP_4]
--将文件分配到分件组，并且打散在不同的硬盘上，以提高i/o并行处理能力
Alter DATABASE Tmp_Db
ADD FILE(NAME= N'Tmp_Db_FILEGROUP_1',FILENAME= N'E:\Data\Tmp_Db_FILEGROUP_1.ndf',SIZE= 3072KB ,FILEGROWTH= 1024KB )
TO FILEGROUP [FILEGROUP_1]
Alter DATABASE Tmp_Db
ADD FILE(NAME= N'Tmp_Db_FILEGROUP_2',FILENAME= N'F:\Data\Tmp_Db_FILEGROUP_2.ndf',SIZE= 3072KB ,FILEGROWTH= 1024KB )
TO FILEGROUP [FILEGROUP_2]
Alter DATABASE Tmp_Db
ADD FILE(NAME= N'Tmp_Db_FILEGROUP_3',FILENAME= N'G:\Data\Tmp_Db_FILEGROUP_3.ndf',SIZE= 3072KB ,FILEGROWTH= 1024KB )
TO FILEGROUP [FILEGROUP_3]
Alter DATABASE Tmp_Db
ADD FILE(NAME= N'Tmp_Db_FILEGROUP_4',FILENAME= N'H:\Data\Tmp_Db_FILEGROUP_4.ndf',SIZE= 3072KB ,FILEGROWTH= 1024KB )
TO FILEGROUP [FILEGROUP_4]
二、创建分区函数及分区方案(架构)--此步骤为核心部分
1.--创建分区函数，这里我们是以我们实例的一个表的int型ID的值的范围来分为四个区；
Create PARTITION FUNCTION PT_FN_TEST(INT)
AS RANGE LEFT FOR VALUES(300000,600000,900000)
2.--创建分区方案，将分区函数所分四个区对应（关联）到四个文件组；
Create PARTITION SCHEME SH_TEST AS PARTITION PT_FN_TEST TO
(FILEGROUP_1,FILEGROUP_2,FILEGROUP_3,FILEGROUP_4)
3.--创建分区表,通过分区键值ID和分区方案SH_TEST结合。

Create TABLE T_TEST
(ID INT IDENTITY(1,1)PRIMARY KEY,CLASS_A VARCHAR(50),CLASS_B VARCHAR(50),ADD_DT DATETIME)ON SH_TEST(ID)
三、为表插入数据，查看结果
--这里我们插入100W数据
DECLARE @I INT
Select @I = 1
WHILE @I <= 1000000
BEGIN
Insert INTO T_TEST(CLASS_A,CLASS_B,ADD_DT)
Select CASE @I%2 WHEN 0 THEN'CLASS_A'+CAST(@I AS VARCHAR)ELSE CAST(@I AS VARCHAR)END,
CASE @I%5 WHEN 0 THEN'CLASS_B'+CAST(@I AS VARCHAR)ELSE CAST(@I AS VARCHAR)END,GETD ATE()
Select @I = @I + 1
END
--查看分区信息
Select$partition.PT_FN_TEST(ID)AS PARTITION_NUMBER_ID,MIN(ID)AS MIN_ID,MAX(ID)AS MAX_ID ,count(*)AS PARTITION_CNT
FROM TMP_DB.dbo.T_TEST
GROUP BY$partition.PT_FN_TEST(ID)
ORDER BY PARTITION_NUMBER_ID
--结果
以次为：分区ID，当前分区最小ID,当前分区最大ID,当前分区数据行总数
1 1 300000 300000
2 300001 600000 300000
3 600001 900000 300000
4 900001 1000000 100000
可以看到，有T_TEST以被分到四个分区，也即被存储在Tmp_Db_FILEGROUP_1.ndf、Tmp_Db_FILEGROUP_2.ndf、Tmp_Db_FILEGROUP_3.ndf、Tmp_Db_FILEGROUP_4.ndf四个物理文件上，分存在四个物理硬盘。

到此，分区表建立完成。

最后说明一下，分区表是将一个表分配到不同的物理硬盘上，利用多个i/o的并行处理，来提高数据处理能力，如果你只
有一个硬盘，假如做了分区表，性能只能变得更差，切记。