[edit]
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'
[edit]
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)
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
[edit]
3 #
- 2008 r2覯 一 1000 螳蟾讌襷 讌.
- 2008 r2 sp1 一 15,000螳蟾讌 讌襦 覲蟆暑り . 企. (2011-06-09) 誤 伎
![[http]](/moniwiki/imgs/http.png)
蠍襯 企Ν..
蠍襯 覲企 ろ螳 . "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
[edit]
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蟇企 曙 蟆 覲伎 蟆企.
鏤