[edit]
1 一 蟯襴 #
![[http]](/moniwiki/imgs/http.png)
れ SQL SEVER MVP 53(http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788996276586&orderClick=LAG) 豈 谿瑚..れ SQL SEVER MVP 53(http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788996276586&orderClick=LAG) 豈 谿瑚..
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'
--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
蠍(http://technet.microsoft.com/en-us/library/gg981694.aspx)襯 企Ν..蠍(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
Automate Sliding Window Partition Management: Part I(http://www.sqlservercentral.com/articles/Partitioning/71655/)Automate Sliding Window Partition Management: Part II(http://www.sqlservercentral.com/articles/Partitioning/71656/)Automate Sliding Window Partition Management: Part III(http://www.sqlservercentral.com/articles/Partitioning/71657/)How To Decide if You Should Use Table Partitioning(http://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+BrentOzar-SqlServerDba+%28Brent+Ozar+PLF%29)
--一牛 企 襷り鍵
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
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]))
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蟇企 曙 蟆 覲伎 蟆企.