테이블 파티션

테이블 파티셔닝... 그냥..

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]