SQLSERVER2005表分区演示..

SQL SERVER 2005中&＃xff0c;终于引入了表分区&＃xff0c;就是说&＃xff0c;当一个表里的数据很多时&＃xff0c;可以将其分拆到
多个的表里&＃xff0c;大大提高了性能。下面举例子说明之

比如&＃xff0c;在C盘下建立如下几个目录
C:Data2Primary
C:Data2FG1
C:Data2FG2
C:Data2FG3
C:Data2FG4

其中primary存放的是主数据库文件&＃xff0c;其他FG1--FG4存放四个单独的文件组&＃xff0c;可以见创立数据库
Data Partition DB2&＃xff0c;如下

USE [master]
GO
/****** Object:  Database [Data Partition DB]    Script Date: 10/08/2006 23:09:53 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name &＃61; N&＃39;Data Partition DB2&＃39;)
DROP DATABASE [Data Partition DB2]
GO
CREATE DATABASE [Data Partition DB2]
ON PRIMARY
(NAME&＃61;&＃39;Data Partition DB Primary FG&＃39;,
FILENAME&＃61;
&＃39;C:Data2PrimaryData Partition DB Primary FG.mdf&＃39;,
SIZE&＃61;5,
MAXSIZE&＃61;500,
FILEGROWTH&＃61;1 ),
FILEGROUP [Data Partition DB FG1]
(NAME &＃61; &＃39;Data Partition DB FG1&＃39;,
FILENAME &＃61;
&＃39;C:Data2FG1Data Partition DB FG1.ndf&＃39;,
SIZE &＃61; 5MB,
MAXSIZE&＃61;500,
FILEGROWTH&＃61;1 ),
FILEGROUP [Data Partition DB FG2]
(NAME &＃61; &＃39;Data Partition DB FG2&＃39;,
FILENAME &＃61;
&＃39;C:Data2FG2Data Partition DB FG2.ndf&＃39;,
SIZE &＃61; 5MB,
MAXSIZE&＃61;500,
FILEGROWTH&＃61;1 ),
FILEGROUP [Data Partition DB FG3]
(NAME &＃61; &＃39;Data Partition DB FG3&＃39;,
FILENAME &＃61;
&＃39;C:Data2FG3Data Partition DB FG3.ndf&＃39;,
SIZE &＃61; 5MB,
MAXSIZE&＃61;500,
FILEGROWTH&＃61;1 ),
FILEGROUP [Data Partition DB FG4]
(NAME &＃61; &＃39;Data Partition DB FG4&＃39;,
FILENAME &＃61;
&＃39;C:Data2FG4Data Partition DB FG4.ndf&＃39;,
SIZE &＃61; 5MB,
MAXSIZE&＃61;500,
FILEGROWTH&＃61;1 )
接下来&＃xff0c;我们创建分区表函数&＃xff0c;这其实可以理解为一个规则&＃xff0c;说明如何以一个规则来将一个表来划分&＃xff0c;如下 use [Data Partition DB2]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int)
AS RANGE LEFT FOR VALUES (100,200,300)
其中分区函数的名称是Data Partition Range,后面的类型&＃xff08;int)表明接下来用来分区的那个字段的类型是INT类型&＃xff0c;
而VALUES (100,200,300)表明&＃xff0c;将把表分为4个区了&＃xff0c;是从负数到100&＃xff0c;100-200&＃xff0c;200-300&＃xff0c;大于300。
接下来&＃xff0c;我们要创建分区架构&＃xff0c;即将分区函数应用到我们分好的四个文件组里面去
USE [Data Partition DB2]
go
CREATE PARTITION SCHEME [Data Partition Scheme]
AS PARTITION [Data Partition Range]
TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3],[Data Partition DB FG4]);
 
再创建表的结构USE [Data Partition DB2]
go
CREATE TABLE MyTable
(ID INT NOT NULL, Date DATETIME, Cost money)    
   ON [Data Partition Scheme] (ID);

这里注意&＃xff0c;ON [Data Partition Scheme] (ID);表明&＃xff0c;划分时以ID的大小作为划分的根据&＃xff0c;ON后要跟分区架购的名称

最后&＃xff0c;我们可以填充数据了

USE [Data Partition DB2]
go
declare &＃64;count int
set &＃64;count &＃61;-25
while &＃64;count <&＃61;100
begin
insert into MyTable select &＃64;count,getdate(),100.00
set &＃64;count&＃61;&＃64;count&＃43;1
end
set &＃64;count &＃61;101
while &＃64;count <&＃61;200
begin
insert into MyTable select &＃64;count,getdate(),200.00
set &＃64;count&＃61;&＃64;count&＃43;1
end
set &＃64;count &＃61;201
while &＃64;count <&＃61;300
begin
insert into MyTable select &＃64;count,getdate(),300.00
set &＃64;count&＃61;&＃64;count&＃43;1
end
set &＃64;count &＃61;301
while &＃64;count <&＃61;400
begin
insert into MyTable select &＃64;count,getdate(),400.00
set &＃64;count&＃61;&＃64;count&＃43;1
end
set &＃64;count &＃61;401
while &＃64;count <&＃61;800
begin
insert into MyTable select &＃64;count,getdate(),500.00
set &＃64;count&＃61;&＃64;count&＃43;1
end

最后&＃xff0c;我们可以查询下&＃xff0c;插入的这些数据&＃xff0c;是否真的被划分到四个不同的文件组里的表分区了&＃xff0c;可以这样看
SELECT *, $PARTITION.[Data Partition Range](ID)
 FROM MyTable