Contents

1 一 蟯襴
1.1
1.2
1.3
2 meta data
3
4 谿瑚襭
5 一 譯殊


1 一 蟯襴 #

[http]れ SQL SEVER MVP 53(http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788996276586&orderClick=LAG) 豈 谿瑚..
1.1 #
  1. 狩 一危磯伎れ 企 一危磯ゼ
  2. SPLIT覈轟朱 螳 襦 觜 一
  3. SWITCH覈轟朱 企螻 一

1.2 #
  1. 狩 一危磯伎れ 觜 企
  2. SWITCH覈轟 伎 螳 る 一螻 觜 企 蟲豌
  3. MERGE 覈轟 伎 る 一螻 伎 一 覲

1.3 #
drop table p_test;
drop partition scheme ps_test;
drop partition function pf_test;
go

create partition function pf_test(date)
as range right for values
(
	'20101101'
,	'20101102'
,	'20101103'
);

create partition scheme ps_test 
as partition pf_test
--to ([primary],[primary],[primary],[primary]);
all to ([primary]);

create table p_test(dt date, id varchar(20))
on ps_test(dt);

insert p_test values('20101101', 'a');
insert p_test values('20101102', 'b');
insert p_test values('20101103', 'c');

drop index p_test.nix_id
create index nix_id on p_test(id,dt)--覿 dt螳 伎朱 
on ps_test(dt)

select *, $partition.pf_test(dt) from p_test
go

/*

1. 狩 一危磯伎れ  企 一危磯ゼ 
2. SPLIT覈轟朱 螳  襦 觜 一 
3. SWITCH覈轟朱 企螻 一 
*/

--1. 狩 一危磯伎れ  企 一危磯ゼ 
drop table dbo.p_switch;
create table dbo.p_switch(dt date, id varchar(20))
on [primary]

insert p_switch values('20101104', 'd');

drop index p_switch.nix_id
create index nix_id on p_switch(id, dt) --覿 dt螳 伎朱 
--on ps_test(dt)

alter table dbo.p_switch add constraint ck1 
check(dt >= '20101104' and dt is not null);


--2. SPLIT覈轟朱 螳  襦 觜 一 
alter partition scheme ps_test next used [primary];
alter partition function pf_test() split range('20101104');

--3. SWITCH覈轟朱 企螻 一 
alter table p_switch switch to p_test partition 5

select * 
from sys.partition_functions a
	inner join sys.partition_range_values b
		on a.function_id = b.function_id
where name = 'pf_test'


/*

1. 狩 一危磯伎れ 觜  企  
2. SWITCH覈轟 伎 螳 る 一螻 觜 企 蟲豌
3. MERGE 覈轟 伎 る 一螻 伎 一 覲
*/

--1. 狩 一危磯伎れ 觜  企  
drop table dbo.p_drop;
create table dbo.p_drop(dt date, id varchar(20));

--2. SWITCH覈轟 伎 螳 る 一螻 觜 企 蟲豌
alter table p_test switch partition 2 to p_drop;

--3. MERGE 覈轟 伎 る 一螻 伎 一 覲
alter partition function pf_test() merge range('20101102')


select * 
from sys.partition_functions a
	inner join sys.partition_range_values b
		on a.function_id = b.function_id
where name = 'pf_test'

2 meta data #

http://blogs.solidq.com/rdyess/Post.aspx?ID=4&title=Table+Partitioning+and+Indexes+(Part+2)
http://blogs.solidq.com/rdyess/Post.aspx?ID=5&title=Table+Partitioning+and+Indexes+(Part+1)
--review the indexes just created
select 
    object_name(i.[object_id]) 'table name'
,   i.[name] as 'index name'
,   i.index_id as 'index id'
,   i.type_desc as 'index type'
,   ic.index_column_id as 'column'
,   c.[name] as 'column name'
,   ic.key_ordinal as 'key column'
,   ic.is_included_column as 'include column'
,   ic.partition_ordinal as 'partitioning column'--partitioning column added to index or part of key
from sys.indexes i
    inner join sys.index_columns ic
        on i.[object_id] = ic.[object_id]
       and i.index_id = ic.index_id
    inner join sys.columns c
        on ic.[object_id] = c.[object_id]
        and ic.column_id = c.column_id
order by i.name, i.index_id,ic.index_column_id


select 
    pf.[name]
,   pf.[type] --what type of partitioning function
,   pf.type_desc --what type of partitioning function
,   pf.fanout --how many partitions do we have
,   pf.boundary_value_on_right --1 for right, 0 for left
,   pf.create_date
,   pf.modify_date
,   rv.boundary_id
,   rv.[value]
,   t.[name]
,   s.[name]
,   pp.max_length
,   pp.[precision]
,   pp.scale
,   pp.collation_name
,   t.is_nullable
,   t.is_user_defined
,   t.is_assembly_type
from sys.partition_functions pf
    inner join sys.partition_parameters pp
        on pf.function_id = pp.function_id
    inner join sys.types t
        on pp.system_type_id = t.system_type_id
    inner join sys.schemas s
        on t.schema_id = s.schema_id
    inner join sys.partition_range_values rv
        on pp.function_id = rv.function_id
        and pp.parameter_id = rv.parameter_id
order by pf.[name], rv.boundary_id

3 #

  • 2008 r2覯 一 1000 螳蟾讌襷 讌.
  • 2008 r2 sp1 一 15,000螳蟾讌 讌襦 覲蟆暑り . 企. (2011-06-09) 誤 伎 [http](http://technet.microsoft.com/en-us/library/gg981694.aspx)襯 企Ν..

2008 r2 sp1 伎 一 15,000螳蟾讌 る れ sp襯 ろ伎 . 譯殊 [http](http://msdn.microsoft.com/ko-kr/library/hh204563.aspx)襯 覲企 ろ螳 . "sp_db_increased_partitions"碁 "sp_dp_increased_partitions" 螳 .
select @@version
/*
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
	Jun 17 2011 00:54:03 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
*/

exec sp_db_increased_partitions 
    @dbname='admin' 
,   @increased_partitions='true' --true/on || false/off

4 谿瑚襭 #

5 一 譯殊 #

一 企 蟆曙一 所 譯殊襯 . SQL覓語 燕 '一' 螳伎朱 磯Μ螳 襦 牛磯伎襯 讌願 . 襯 れ, れ螻 螳 蟆曙磯.

select max(col_a) from partition_table_a

れ襦 企慨覃 れ螻 螳. (伎 一 襷 企 所 覲蟆渚伎 .)

--一牛 企 襷り鍵
drop table p_test;
drop partition scheme ps_test;
drop partition function pf_test;

create partition function pf_test(date)
as range right for values
(
	'20101101'
,	'20101102'
,	'20101103'
);

create partition scheme ps_test 
as partition pf_test
to ([primary],[primary],[primary],[primary]);

create table p_test(dt date, cola char(500))
on ps_test(dt);

insert p_test values('20101101', 'aa')
go 100
insert p_test values('20101102', 'aa')
go 100
insert p_test values('20101103', 'aa')
go 100

れ sql覓語 ろ 覲願 ろ螻 危エ覲伎
select max(dt) from p_test

Rows                 Executes             StmtText                                                                   
-------------------- -------------------- ---------------------------------------------------------------------------
1                    1                    select max(dt) from p_test                                                 
1                    1                      |--Stream Aggregate(DEFINE:([Expr1005]=MAX([work].[dbo].[p_test].[dt]))) 
300                  1                           |--Table Scan(OBJECT:([work].[dbo].[p_test]))                       

Rows貉殊 覲企 300蟇(れ) 所螻 蟆郁骸襯 詞. 願 覦 企 1蟇企 暑讌 3蟇企 曙伎 max(dt)螳 譟壱 蟆伎. 蠏碁 企至 願屋蟾? 螳. metadata襯 伎 覦覯企. 覃 partition 蟆所 ロ dummy企 伎 覦覯企. 蠍一 metadata襯 伎企慨.

select max(b.dt)
from sys.partitions a
	cross apply (
		select top 1 dt
		from p_test
		where $partition.pf_test(dt) = a.partition_number
	) b
where object_id = object_id('dbo.p_test')

Rows                 Executes             StmtText                                                                         
-------------------- -------------------- ---------------------------------------------------------------------------------
1                    1                      |--Stream Aggregate(DEFINE:([Expr1010]=MAX([work].[dbo].[p_test].[dt])))       
3                    1                           |--Nested Loops(Inner Join, OUTER REFERENCES:([rs].[numpart]))            
4                    1                                |--Clustered Index Scan(OBJECT:([work].[sys].[sysrowsets].[clust] AS 
3                    4                                |--Top(TOP EXPRESSION:((1)))                                         
3                    4                                     |--Table Scan(OBJECT:([work].[dbo].[p_test]), SEEK:([PtnId1006]=
sys.partitions 4蟇伎 曙螻, p_test 企 3蟇企 曙 蟆 覲伎 蟆企.