Simple Partition Table Example
This example shows how to create a simple partitioned table. This table has one filegroup for projects with a budget for less than 200,000.0000 and another table for projects with a budget of 200,000.0000 or more.
use master
go
drop database partExample
go
create database partExample
on primary
( name = partExampleDB1,
filename = N'c:\esqlexamples\partExampleFile1.mdf',
size = 5mb,maxsize = 10mb,filegrowth = 10%
),
filegroup partExampleFG2
( name = partExampleDB2,
filename = N'c:\esqlexamples\partExampleFile2.ndf',
size = 5mb,maxsize = 10mb,filegrowth = 10%
)
log on
( name = partExampleLog,
filename = N'c:\esqlexamples\partExampleFile.ldf',
size = 2mb,
maxsize = 5mb,
filegrowth = 1mb)
go
use partExample
go
drop table dbo.proj
go
drop partition scheme BudgetPartitionSchemer
go
drop partition function BudgetPartitioner
go
create partition function BudgetPartitioner(money)
as range left for values (200000.0)
go
create partition scheme BudgetPartitionSchemer
as partition BudgetPartitioner
to ([primary],partExampleFG2)
go
create table dbo.proj
(
jno tinyint identity(1,1),
jname nvarchar(100) not null,
budget money not null,
loc nvarchar(100) not null,
constraint pkProj_jno unique (jno,budget)
)
on BudgetPartitionSchemer(budget)
go
insert into dbo.proj values ('easy project',25000.0,'Omaha')
insert into dbo.proj values ('hard project',250000.0,'Los Angeles')
insert into dbo.proj values ('expensive project',2500000.0,'Boston')
select $partition.BudgetPartitioner(budget) as 'src part #',* from dbo.proj
go