SQL Server 2005分区
分区示例一(网上查的)
1、创建数据文件及数据库
在C盘下建立如下几个目录
C:\Data2\Primary
C:\Data2\FG1
C:\Data2\FG2
C:\Data2\FG3
C:\Data2\FG4
其中primary存放的是主数据库文件，其他FG1--FG4存放四个单独的文件组，可以见创立数据库
Data Partition DB2，如下
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 = N'Data Partition DB2')
DROP DATABASE [Data Partition DB2]
GO
CREATE DATABASE [Data Partition DB2]
ON PRIMARY
(NAME='Data Partition DB Primary FG',
FILENAME=
'C:\Data2\Primary\Data Partition DB Primary FG.mdf',
SIZE=5,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG1]
(NAME = 'Data Partition DB FG1',
FILENAME =
'C:\Data2\FG1\Data Partition DB FG1.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG2]
(NAME = 'Data Partition DB FG2',
FILENAME =
'C:\Data2\FG2\Data Partition DB FG2.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG3]
(NAME = 'Data Partition DB FG3',
FILENAME =
'C:\Data2\FG3\Data Partition DB FG3.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG4]
(NAME = 'Data Partition DB FG4',
FILENAME =
'C:\Data2\FG4\Data Partition DB FG4.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )
2、创建分区表函数
这其实可以理解为一个规则，说明如何以一个规则来将一个表来划分，如下
use [Data Partition DB2]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int)
AS RANGE LEFT FOR VALUES (100,200,300)
其中分区函数的名称是Data Partition Range,后面的类型（int)表明接下来用来分区的那个字段的类型是INT类型，
而VALUES (100,200,300)表明，将把表分为4个区了，是从负数到100，100-200，200-300，大于300。
3、创建分区架构
即将分区函数应用到我们分好的四个文件组里面去
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]);
4、创建表结构
USE [Data Partition DB2]
go
CREATE TABLE MyTable
(ID INT NOT NULL, Date DATETIME, Cost money)
ON [Data Partition Scheme] (ID);
这里注意，ON [Data Partition Scheme] (ID);表明，划分时以ID的大小作为划分的根据，ON后要跟分区架购的名称
分区示例二(做技术调查时做的)
在已建好的数据库中新加表做设置分区:
1、创建文件组：
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2007_3]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2007_4]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2008_1]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2008_2]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2008_3]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2008_4]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2009_1]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2009_2]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2009_3]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GF2009_4]
GO
ALTER DATABASE [PTPS]
ADD FILEGROUP [GFMAX]
GO
2、创建数据文件：
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2007_3,
FILENAME = 'D:\Temp\PTPSDATA\F2007_3.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2007_3]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2007_4,
FILENAME = 'D:\Temp\PTPSDATA\F2007_4.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2007_4]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2008_1,
FILENAME = 'D:\Temp\PTPSDATA\F2008_1.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2008_1]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2008_2,
FILENAME = 'D:\Temp\PTPSDATA\F2008_2.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2008_2]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2008_3,
FILENAME = 'D:\Temp\PTPSDATA\F2008_3.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2008_3]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2008_4,
FILENAME = 'D:\Temp\PTPSDATA\F2008_4.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2008_4]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2009_1,
FILENAME = 'D:\Temp\PTPSDATA\F2009_1.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2009_1]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2009_2,
FILENAME = 'D:\Temp\PTPSDATA\F2009_2.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2009_2]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2009_3,
FILENAME = 'D:\Temp\PTPSDATA\F2009_3.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2009_3]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = F2009_4,
FILENAME = 'D:\Temp\PTPSDATA\F2009_4.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GF2009_4]
GO
ALTER DATABASE [PTPS]
ADD FILE
(NAME = FMAX,
FILENAME = 'D:\Temp\PTPSDATA\FMAX.ndf',
SIZE = 1MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
TO FILEGROUP [GFMAX]
GO
3、创建分区函数：
USE [PTPS]
GO
CREATE PARTITION FUNCTION InpHistPartition(datetime)
AS
RANGE LEFT FOR VALUES (
'20070930 23:59:59.997', --2007 第三季度
'20071231 23:59:59.997', --2007 第四季度
'20080331 23:59:59.997', --2008 第一季度
'20080630 23:59:59.997', --2008 第二季度
'20080930 23:59:59.997', --2008 第三季度
'20081231 23:59:59.997', --2008 第四季度
'20090331 23:59:59.997', --2009 第一季度
'20090630 23:59:59.997', --2009 第二季度
'20090930 23:59:59.997', --2009 第三季度
'20091231 23:59:59.997' --2009 第四季度
)
GO
4、创建分区框架：
USE [PTPS]
GO
CREATE PARTITION SCHEME [InpHistScheme]
AS PARTITION [InpHistPartition]
TO
(
[GF2007_3],
[GF2007_4],
[GF2008_1],
[GF2008_2],
[GF2008_3],
[GF2008_4],
[GF2009_1],
[GF2009_2],
[GF2009_3],
[GF2009_4],
[GFMAX]
);
GO
5、新加数据库表：
说明：
数据库表中最多允许创建1024个字段；
一张表中所有字段的字节总数不允许大于8060字节（二进制流等不包括在内）；
USE [PTPS]
GO
CREATE TABLE [dbo].[TM_INP](
[INPCreateTime] [datetime] NOT NULL,
[InpValue1] [float] NULL,
[InpState1] [float] NULL,
........
[InpValue200] [float] NULL,
[InpState200] [float] NULL
) ON [InpHistScheme](INPCreateTime)
