테이블 파티셔닝... 그냥..
use master
if object_id('multiTest') is not null drop database multiTest
create database multiTEST
on primary
( name = 'multiTest_1',
filename = 'e:\200. multibase\multiTest_1.mdf',
size = 30mb,
filegrowth = 5mb),
( name = 'multiTest_2',
filename = 'e:\200. multibase\multiTest_2.ndf',
size = 30mb,
filegrowth = 5mb)
log on
( name = 'multiTest_log',
filename = 'e:\200. multibase\mutlTest.log',
size = 10mb,
filegrowth = 3mb)
alter database multiTEST
add filegroup FG2
alter database multiTest add file
( name = 'multiTEST_FG2',
filename = 'E:\200. MultiBase\multiTest_FG2.mdf',
size = 10mb,
filegrowth = 3mb
) to filegroup FG2
sp_helpdb multiTest
create table Persons
(
prs_id int not null primary key,
prs_name varchar(30)
) on 'primary'
create table Department
(
dpt_id int not null primary key,
dpt_name varchar(30)
) on 'FG2'
sp_helpdb multiTest
sp_helpindex persons
alter database multiTest set offline
use multiTest
alter database multiTest set online
alter table Persons column add dpt_id int
sp_helpindex department
declare @int int
set @int = 10001
while @int < 3000 * 10
begin
insert department values(@int, replicate('p', 10))
set @int = @int + 1
end
select * from persons
select * from department
sp_helpdb multiTest
sp_help persons
select * from $partition('FG2', 1)
sp_helpfile multiTest_Fg2
alter database multiTest add file
( name = 'multiTest_FG3',
filename = 'E:\200. MultiBase\multiTest_FG3.mdf',
size = 10mb,
filegrowth = 3mb
) to filegroup FG2
alter database multiTest add file
( name = 'multiTEST_FG2',
filename = 'E:\200. MultiBase\multiTest_FG2.mdf',
size = 10mb,
filegrowth = 3mb
) to filegroup FG2
alter database multiTest add filegroup FG3
alter database multiTest add file
( name = 'multiTEST_FG3_1',
filename = 'E:\200. MultiBase\multiTest_FG3_1.mdf',
size = 10mb,
filegrowth = 3mb
) to filegroup FG3
alter database multiTest add file
( name = 'multiTEST_FG3_2',
filename = 'E:\200. MultiBase\multiTest_FG3_2.mdf',
size = 10mb,
filegrowth = 3mb
) to filegroup FG3
alter database multiTest add filegroup FG4
alter database multiTest add file
( name = 'multiTEST_FG4_1',
filename = 'E:\200. MultiBase\multiTest_FG4_1.mdf',
size = 10mb,
filegrowth = 3mb
) to filegroup FG4
alter database multiTest add file
( name = 'multiTEST_FG4_2',
filename = 'E:\200. MultiBase\multiTest_FG4_2.mdf',
size = 10mb,
filegrowth = 3mb
) to filegroup FG4
drop partition function DepartmentPF
begin tran
create partition function DepartmentPF(int) as range left for values (10000, 20000)
create partition scheme DepartmentPS as partition DepartmentPF to ([primary], FG3, FG4)
commit tran
sp_helpdb multiTest
alter table Department on DepartmentPS
create table
sp_helpdb tempdb
alter database tempdb add file
( name = 'tempdev2',
filename = 'E:\200. MultiBase\tempdb_2.ndf',
size = 30mb,
filegrowth = 5mb
) to filegroup [primary]
alter database tempdb add file
( name = 'tempdev3',
filename = 'E:\200. MultiBase\tempdb_3.ndf',
size = 30mb,
filegrowth = 5mb
) to filegroup [primary]
'Brain Trainning > DataBase' 카테고리의 다른 글
쿼리 및 시스템 튜닝 자료 (0) | 2009.11.12 |
---|---|
Sqlserver Host명 변경 (0) | 2009.11.12 |
Snapshot 생성 (0) | 2009.11.11 |
Backup 및 Restore (0) | 2009.11.10 |
[Oracle] Connect by prior ... 예제 (0) | 2009.09.08 |