/**********************************************************************************************
-- Title : [2k5] 파티션 테이블 생성 및 스위칭, 확인, 삭제
-- Reference : hanbitbook.co.kr
-- Key word : partition table function schema 파티션 테이블 함수 스키마 switch
**********************************************************************************************/
use master;
go
if exists(select * from master.dbo.sysdatabases where name = 'parttbldb')
drop database parttbldb;
go
-- db 생성
create database parttbldb
on primary
( name = parttbldb, -- 75년이전출생자(75년포함)
filename = N'd:\parttbldb.mdf'
),
filegroup year_76_80 -- 76~80년출생자
( name = parttbldb2,
filename = N'd:\parttbldb2.ndf'
),
filegroup year_81 -- 81년이후출생자(81년포함)
( name = parttbldb3,
filename = N'd:\parttbldb3.ndf'
)
log on
( name = parttbldb_log, -- 로그파일
filename = N'd:\parttbldb_log.ldf'
);
go
use parttbldb;
go
-- 파티션 함수 생성
create partition function birtyyearrangepf (int)
as range left for values (1975, 1981);
go
-- 파티션 스키마 생성
create partition scheme birtyyearrangeps
as partition birtyyearrangepf
to ([primary], year_76_80, year_81);
go
-- 파티션된 테이블 생성
create table usertbl
( userid nchar(8) not null ,
name nvarchar(10) not null,
birthyear int not null,
addr nchar(4) not null,
mobile1 nchar(3),
mobile2 nchar(8),
height smallint
) on birtyyearrangeps (birthyear);
go
insert into usertbl
select N'ajh', N'안정환', 1979, N'강원', null, null, 182 union all
select N'cjc', N'최진철', 1975, N'제주', N'019', N'0000000 ', 185 union all
select N'jjj', N'조재진', 1986, N'충북', N'019', N'3333333 ', 179 union all
select N'kni', N'김남일', 1977, N'경북', N'016', N'6666666 ', 183 union all
select N'lcs', N'이천수', 1983, N'인천', N'011', N'4444444 ', 179 union all
select N'lyp', N'이영표', 1983, N'전북', null, null, 178 union all
select N'pjs', N'박지성', 1983, N'서울', N'011', N'1111111 ', 181 union all
select N'pjy', N'박주영', 1986, N'경기', N'011', N'2222222 ', 178 union all
select N'sjk', N'송종국', 1979, N'경기', N'018', N'9999999 ', 178 union all
select N'skh', N'설기현', 1978, N'서울', N'011', N'8888888 ', 182;
go
-- 전체 보기
select * from usertbl;
go
-- 파티션 내용 보기
select * from usertbl
where $partition.birtyyearrangepf(birthyear) = 1;
select * from usertbl
where $partition.birtyyearrangepf(birthyear) = 2;
select * from usertbl
where $partition.birtyyearrangepf(birthyear) = 3;
go
-- 파티션 번호 가져오기
select $partition.birtyyearrangepf (1981) ;
go
-- 파티션별 데이터 건수 확인
select $partition.birtyyearrangepf(birthyear) as partition
, count(*) as [count] from usertbl
group by $partition.birtyyearrangepf(birthyear)
order by partition ;
go
-- 일반 테이블 생성
create table nopart_usertbl
( userid nchar(8) not null ,
name nvarchar(10) not null,
birthyear int not null,
addr nchar(4) not null,
mobile1 nchar(3),
mobile2 nchar(8),
height smallint
) on year_81;
go
-- 3번 파티션 테이블과 일반 테이블의 스위칭
alter table usertbl
switch partition 3 to nopart_usertbl;
go
-- 파티션된 테이블관 스위칭된 테이블 확인
select * from usertbl
where $partition.birtyyearrangepf(birthyear) = 3;
select * from usertbl;
select * from nopart_usertbl;
go
insert into usertbl
select * from nopart_usertbl;
go
select * from usertbl
where $partition.birtyyearrangepf(birthyear) = 3;
go
select * from usertbl;
go
-- 파티션 데이블 확인(fr. Ctlg View)
select * from sys.partition_functions;
select * from sys.partition_parameters;
select * from sys.partition_range_values;
select * from sys.partitions where object_id = object_id('usertbl');
-- 테이블 삭제
drop table usertbl, nopart_usertbl;
go
-- 파티션 스키마 삭제
drop partition scheme birtyyearrangeps;
go
-- 파티션 함수 삭제
drop partition function birtyyearrangepf;
go