--创建文件组
create database alex2
on primary
(name='alex',filename='c:\Data\alex.mdf',size=3mb,maxsize=10mb,filegrowth=1mb),
Filegroup old
(name='olddb',filename='c:\Data\olddb.ndf',size=1mb,maxsize=10mb,filegrowth=1mb),
Filegroup first
(name='firstdb',filename='c:\Data\firstdb.ndf',size=1mb,maxsize=10mb,filegrowth=1mb),
Filegroup second
(name='seconddb',filename='c:\Data\seconddb.ndf',size=1mb,maxsize=10mb,filegrowth=1mb),
Filegroup third
(name='thirddb',filename='c:\Data\thirddb.ndf',size=1mb,maxsize=10mb,filegrowth=10%),
Filegroup fourth
(name='fourthdb',filename='c:\Data\fourthdb.ndf',size=1mb,maxsize=10mb,filegrowth=10%)
log on
(name='alex_log',filename='c:\Data\alex_log.ldf',size=1mb,maxsize=10mb,filegrowth=1mb)
go
--使用数据库
use alex2
go
--创建分区函数
--按照一定的条件划分数据
--range left (1900-01-01,1996-01-01] (1996-01-01,2000-01-01] 分界点的值归左边
--range right (1900-01-01,1996-01-01) [1996-01-01,2000-01-01) 分界点的值归右边
create partition function RateChngDate(datetime)
as range right for values('1996-01-01','2000-01-01','2004-01-01','2008-01-01')
--创建分区方案
--将分区函数区分的范围和文件组对应起来
create partition scheme RateChngDate_Scheme
as partition RateChngDate to (Old,First,Second,Third,Fourth)
--创建分区表
create table EmpPayHistPart
(
EmployeeID int,
RateChangeDate datetime,
Rate money