블로그 "공감"

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

쩝... 클러스터인덱스 왜 Drop_existing을...

select *
 into mem
from member

sp_helpindex member
sp_helpindex mem

select * from mem

create unique clustered index IDX_Mem on mem(member_no)

select member_no, firstname, expr_dt
 from mem
where lastname < 'c'
order by member_no desc

select member_no, lastname --,firstname 
 from mem
where lastname < 'c'
order by member_no desc

create index IX_Mem on mem(lastname)
create index IX_Mem on mem( lastname, firstname) with drop_existing
drop index mem.IX_Mem

create unique clustered index IDX_Mem on mem(member_no desc) with (sort_in_tempdb=on, online=on, drop_existing =on)

select * from sys.sysindexes where id = object_id('mem')


dbcc traceon(3604)
select convert(int, 0x)
dbcc page(Credit, 1, 5312, 3)