[edit]
5 谿瑚襭 #
![[http]](/moniwiki/imgs/http.png)
Solving the Running Totals Problem in SQL Server 2005 with Partitioned Tables(http://www.sqlservercentral.com/articles/T-SQL/65522/)
- Partitioning in SQL Server 2008(http://www.sqlservercentral.com/articles/partition/64740/)
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'SalesHashPF1')
DROP PARTITION FUNCTION SalesHashPF1
GO
CREATE PARTITION FUNCTION SalesHashPF1(int)
AS RANGE RIGHT FOR VALUES (10000, 20000, 30000, 40000, 50000)
GO
--RANGE RIGHT: ~ 1999, 10000 ~ 29999 ....
SELECT
$partition.SalesHashPF1(1) PartitionNo
, $partition.SalesHashPF1(10000) PartitionNo
/*
PartitionNo PartitionNo
----------- -----------
1 2
(1螳 )
*/
create partition function Pf_ConnectHistory(int) as range right for values ( 20090101,20090201,20090301) go create partition scheme Ps_ConnectHistory as partition Pf_ConnectHistory all to([primary]) go CREATE TABLE [Common].[Fact_ConnectHistory2]( [LoginTime] [datetime] NULL, [LogoutTime] [datetime] NULL, [AccountKey] [bigint] NULL, [IP] [binary](4) NULL, [ServiceKey] [int] NULL, [PCRoomKey] [int] NULL, [DateKey] [int] NULL ) ON Ps_ConnectHistory(DateKey) go create clustered index CIX_DateKey on Common.Fact_ConnectHistory2(DateKey) create index NIX_AccountKey_ServiceKey on Common.Fact_ConnectHistory2(AccountKey, ServiceKey) ON Ps_ConnectHistory(DateKey)
create partition function Pf_ConnectHistory(int) as range right for values ( 20090101,20090201,20090301,20090401,20090501,20090601,20090701,20090801,20090901,20091001,20091101,20091201,20100101,20100201,20100301,20100401,20100501,20100601,20100701,20100801,20100901,20101001,20101101,20101201,20110101,20110201,20110301,20110401,20110501,20110601,20110701,20110801,20110901,20111001,20111101,20111201,20120101,20120201,20120301,20120401,20120501,20120601,20120701,20120801,20120901,20121001,20121101,20121201,20130101,20130201,20130301,20130401,20130501,20130601,20130701,20130801,20130901,20131001,20131101,20131201,20140101,20140201,20140301,20140401,20140501,20140601,20140701,20140801,20140901,20141001,20141101,20141201,20150101,20150201,20150301,20150401,20150501,20150601,20150701,20150801,20150901,20151001,20151101,20151201,20160101,20160201,20160301,20160401,20160501,20160601,20160701,20160801,20160901,20161001,20161101,20161201,20170101,20170201,20170301,20170401,20170501,20170601,20170701,20170801,20170901,20171001,20171101,20171201,20180101,20180201,20180301,20180401,20180501,20180601,20180701,20180801,20180901,20181001,20181101,20181201,20190101,20190201,20190301,20190401,20190501,20190601,20190701,20190801,20190901,20191001,20191101,20191201,20200101,20200201,20200301,20200401,20200501,20200601,20200701,20200801,20200901,20201001,20201101,20201201,20210101,20210201,20210301,20210401,20210501,20210601,20210701,20210801,20210901,20211001,20211101,20211201,20220101,20220201,20220301,20220401,20220501,20220601,20220701,20220801,20220901,20221001,20221101,20221201,20230101,20230201,20230301,20230401,20230501,20230601,20230701,20230801,20230901,20231001,20231101,20231201,20240101,20240201,20240301,20240401,20240501,20240601,20240701,20240801,20240901,20241001,20241101,20241201,20250101,20250201,20250301,20250401,20250501,20250601,20250701,20250801,20250901,20251001,20251101,20251201,20260101,20260201,20260301,20260401,20260501,20260601,20260701,20260801,20260901,20261001,20261101,20261201,20270101,20270201,20270301,20270401,20270501,20270601,20270701,20270801,20270901,20271001,20271101,20271201,20280101,20280201,20280301,20280401,20280501,20280601,20280701,20280801,20280901,20281001,20281101,20281201,20290101,20290201,20290301,20290401,20290501,20290601,20290701,20290801,20290901,20291001,20291101,20291201,20300101,20300201,20300301,20300401,20300501,20300601,20300701,20300801,20300901,20301001,20301101,20301201,20310101,20310201,20310301,20310401,20310501,20310601,20310701,20310801,20310901,20311001,20311101,20311201,20320101,20320201,20320301,20320401,20320501,20320601,20320701,20320801,20320901,20321001,20321101,20321201,20330101,20330201,20330301,20330401,20330501,20330601,20330701,20330801,20330901,20331001,20331101,20331201,20340101,20340201,20340301,20340401,20340501,20340601,20340701,20340801,20340901,20341001,20341101,20341201,20350101,20350201,20350301,20350401,20350501,20350601,20350701,20350801,20350901,20351001,20351101,20351201,20360101,20360201,20360301,20360401,20360501,20360601,20360701,20360801,20360901,20361001,20361101,20361201,20370101,20370201,20370301,20370401,20370501,20370601,20370701,20370801,20370901,20371001,20371101,20371201,20380101,20380201,20380301,20380401,20380501,20380601,20380701,20380801,20380901,20381001,20381101,20381201,20390101,20390201,20390301,20390401,20390501,20390601,20390701,20390801,20390901,20391001,20391101,20391201,20400101,20400201,20400301,20400401,20400501,20400601,20400701,20400801,20400901,20401001,20401101,20401201,20410101,20410201,20410301,20410401,20410501,20410601,20410701,20410801,20410901,20411001,20411101,20411201,20420101,20420201,20420301,20420401,20420501,20420601,20420701,20420801,20420901,20421001,20421101,20421201,20430101,20430201,20430301,20430401,20430501,20430601,20430701,20430801,20430901,20431001,20431101,20431201,20440101,20440201,20440301,20440401,20440501,20440601,20440701,20440801,20440901,20441001,20441101,20441201,20450101,20450201,20450301,20450401,20450501,20450601,20450701,20450801,20450901,20451001,20451101,20451201,20460101,20460201,20460301,20460401,20460501,20460601,20460701,20460801,20460901,20461001,20461101,20461201,20470101,20470201,20470301,20470401,20470501,20470601,20470701,20470801,20470901,20471001,20471101,20471201,20480101,20480201,20480301,20480401,20480501,20480601,20480701,20480801,20480901,20481001,20481101,20481201,20490101,20490201,20490301,20490401,20490501,20490601,20490701,20490801,20490901,20491001,20491101,20491201,20500101 ) go create partition scheme Ps_ConnectHistory as partition Pf_ConnectHistory all to([primary]) go --drop table Common.Fact_ConnectHistory2 CREATE TABLE [Common].[Fact_ConnectHistory_Tmp]( [LoginTime] [datetime] NULL, [LogoutTime] [datetime] NULL, [AccountKey] [bigint] NULL, [IP] [binary](4) NULL, [ServiceKey] [int] NULL, [PCRoomKey] [int] NULL, [DateKey] [int] NULL ) ON Ps_ConnectHistory(DateKey) go create clustered index CIX_DateKey on Common.Fact_ConnectHistory2(DateKey) create index NIX_AccountKey_ServiceKey on Common.Fact_ConnectHistory2(AccountKey, ServiceKey) WITH (FILLFACTOR=80) ON Ps_ConnectHistory(DateKey) insert Common.Fact_ConnectHistory2 select top 1 * from Common.Fact_ConnectHistory select top 10 * from Common.Fact_ConnectHistory2 with (nolock) where 1=1 --and accountkey = '78770081912455' and $partition.Pf_ConnectHistory(DateKey) = 16
drop table if exists #partition
select
concat(schema_name(c.schema_id), '.', object_name(a.object_id)) table_name
, a.object_id
, sum(a.rows) rows
, count(*) partitions
, sum(b.used_page_count * 8 / 1024 / 1024) size_gb
into #partition
from sys.partitions a
left join sys.dm_db_partition_stats b
on a.partition_id = b.partition_id
and a.object_id = b.object_id
inner join sys.objects c
on a.object_id = c.object_id
where 1=1
and a.rows > 0
group by
concat(schema_name(c.schema_id), '.', object_name(a.object_id))
, a.object_id
select *
from #partition
order by size_gb desc
--: 一 蟲豌
declare
@std_dt char(8)
, @next_dt char(8)
, @partition_id bigint
, @sql varchar(8000)
set @std_dt = '20200801'
truncate table dbo.temp1
set @next_dt = convert(char(8),dateadd(dd, 1, @std_dt), 112)
set @partition_id = $partition.pf_daily(@std_dt)
set @sql = 'alter table dbo.temp1 add constraint ck2 check(date_key >= ''@std_dt'' and date_key < ''@next_dt'' and date_key is not null);'
set @sql = replace(@sql, '@std_dt', @std_dt)
set @sql = replace(@sql, '@next_dt', @next_dt)
print @sql
--ck
alter table dbo.temp1 drop constraint if exists ck2
exec(@sql)
--clustered index
--drop index if exists cix_date_key on dbo.temp1;
--create clustered index cix_date_key on dbo.temp1(date_key) with (data_compression = page, sort_in_tempdb=on);
--dbo.partitioned_table 轟 一 dbo.temp1螻 覦蠑瑚鍵
set @sql = 'alter table dbo.partitioned_table switch partition @partition_id to dbo.temp1'
set @sql = replace(@sql, '@partition_id', convert(varchar(100), @partition_id))
print @sql
exec(@sql)
--dbo.temp1襯 dbo.partitioned_table 轟 一螻 覦蠑瑚鍵
set @sql = 'alter table dbo.temp1 switch to dbo.partitioned_table partition @partition_id'
set @sql = replace(@sql, '@partition_id', convert(varchar(100), @partition_id))
print @sql
exec(@sql)
Solving the Running Totals Problem in SQL Server 2005 with Partitioned Tables(http://www.sqlservercentral.com/articles/T-SQL/65522/)Partitioning in SQL Server 2008(http://www.sqlservercentral.com/articles/partition/64740/)