블로그 "공감"

create database PTable
on
(
 name = PTable_Data,
 filename = 'E:\90.Sql_SubData\PTable_Data.mdf',
 size = 10mb,
 filegrowth = 1mb 
)
log on
(
 name = PTable_Log,
 filename = 'E:\90.Sql_SubData\PTable_Log.ldf',
 size = 5mb,
 filegrowth = 1mb
)

alter database PTable
 add filegroup F2

alter database PTable
 add filegroup F3

alter database PTable
add file
(
 name = PTable_Data00,
 filename = 'E:\90.Sql_SubData\PTable_Data00.ndf',
 size = 10mb,
 filegrowth = 1mb
) to filegroup f2

alter database PTable
add file
(
 name = PTable_Data01,
 filename = 'E:\90.Sql_SubData\PTable_Data01.ndf',
 size = 10mb,
 filegrowth = 1mb
) to filegroup f3

alter database PTable
add filegroup f4

alter database PTable
add file
(
 name = PTable_Data02,
 filename = 'E:\90.Sql_SubData\PTable_Data02.ndf',
 size = 10mb,
 filegrowth = 1mb
) to filegroup f4

use credit
select * from credit..charge

use PTable

create partition function category_no (int) as range left for values (3, 5, 10)

create partition scheme category_nosc as partition category_no to ([primary], f2, f3, f4)

CREATE TABLE [dbo].[charge](
 [charge_no] int IDENTITY(1,1) NOT NULL,
 [member_no] int NOT NULL,
 [provider_no]  int NOT NULL,
 [category_no] int NOT NULL,
 [charge_dt] datetime NOT NULL,
 [charge_amt] money NOT NULL,
 [statement_no] int NOT NULL CONSTRAINT [charge_statement_no_default]  DEFAULT (0),
 [charge_code] char(2) NOT NULL CONSTRAINT [charge_status_default]  DEFAULT ('  '),
 CONSTRAINT [charge_ident] PRIMARY KEY CLUSTERED
(
 [charge_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON category_nosc (category_no)

insert into charge
 select * from credit.dbo.charge

/* 3번째 파티션의 정보를 확인 함 */
select * from charge where $partition.category_no(category_no) = 3