日韩欧美国产精品免费一二-日韩欧美国产精品亚洲二区-日韩欧美国产精品专区-日韩欧美国产另-日韩欧美国产免费看-日韩欧美国产免费看清风阁

-- 刪除分區(qū)方案IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PSYear')BEGIN    DROP PARTITION SCHEME PSYear;ENDGO-- 創(chuàng)建分區(qū)方案(提前創(chuàng)建文件組及文件)CREATE PARTITION SCHEME PSYear AS PARTITION PFYearTO (FG2021, FG2022, FG2023, FG2024, [PRIMARY]);GO

CREATE TABLE Sales (    SaleID int IDENTITY(1,1),    SaleDate date,    TotalAmount money,    CustomerID int,    ProductID int,    Quantity int,    PRIMARY KEY (SaleDate, SaleID)) ON PSYear (SaleDate);GO

INSERT INTO Sales (SaleDate, TotalAmount, CustomerID, ProductID, Quantity)VALUES ('2018-03-15', 120.50, 1, 101, 2),       ('2019-07-22', 75.00, 2, 102, 1),       ('2020-05-11', 200.00, 3, 103, 5),       ('2021-12-01', 150.00, 4, 104, 3);

SELECT $PARTITION.YearPartitionFunction(SaleDate) AS PartitionNumber, COUNT(*) AS RecordsFROM SalesGROUP BY $PARTITION.YearPartitionFunction(SaleDate);GO

-- SP_HELP SalesALTER INDEX IX_SaleDate ON Sales REBUILD PARTITION = 3;GO

UPDATE STATISTICS Sales (IX_SaleDate) WITH RESAMPLE ON PARTITIONS(3);GO

ALTER TABLE Sales SWITCH PARTITION 10 TO Archive.Sales PARTITION 10;GO

ALTER TABLE [dbo].[Logs] SET (LOCK_ESCALATION=AUTO)GOALTER TABLE [dbo].[IPRequests] SET (LOCK_ESCALATION=AUTO)GO

/******************************** step_1_分區(qū)切換 ***********************************/declare @now date;declare @next_day nvarchar(10);declare @prio_day datetime;declare @next_fg nvarchar(50);declare @sql nvarchar(500);set @now = getdate()set @next_day = convert(varchar(10),@now,120)  --計(jì)算新分區(qū)劃分的時(shí)間set @prio_day = dateadd(d,-10,@now)        --計(jì)算10天前的時(shí)間，該時(shí)間需要合并set @next_fg = N'filegroup_'+convert(nvarchar(10),datediff(D,'2020-01-01',@now)%10+1) --計(jì)算下一個(gè)分區(qū)名稱--  SELECT @now as [now],@next_day as next_day,@prio_day as prio_day,@next_fg as next_fg
SET @sql = N'ALTER PARTITION SCHEME PS_DateTime NEXT USED ' + @next_fg + '; 'set @sql = @sql + N'ALTER PARTITION FUNCTION PF_DateTime() SPLIT RANGE(''' + @next_day + ''');'exec(@sql);--創(chuàng)建新的分區(qū)
--  切換第一個(gè)分區(qū)數(shù)據(jù)到另一個(gè)表（Logs 和 LogsMid 結(jié)構(gòu)和索引要）ALTER TABLE Logs SWITCH PARTITION 1 TO LogsMidALTER TABLE IPRequests SWITCH PARTITION 1 TO IPRequestsMid
--  合并第一個(gè)分區(qū)，完成！ALTER PARTITION FUNCTION PF_DateTime() MERGE RANGE(@prio_day); 

/******************************** step_2_插入到歷史表 ***********************************/--LogsMid 和 IPRequestsMid 為切換的中間表，需要及時(shí)把數(shù)據(jù)遷移到其他表中。insert into T_TempDB.dbo.T_Logs_history select * from dbo.LogsMid;GOinsert into T_TempDB.dbo.T_IPRequests_history select * from dbo.IPRequestsMid;GO
/******************************** step_3_清除切換臨時(shí)數(shù)據(jù) ***********************************/--中間表不保存數(shù)據(jù)truncate table dbo.LogsMid;GOtruncate table dbo.IPRequestsMid;GO
/******************************** step_4_更新統(tǒng)計(jì)信息 ***********************************/update statistics dbo.Logs;GOupdate statistics dbo.IPRequests;GO
/******************************** step_5_刪除一月前歷史數(shù)據(jù) ***********************************/DELETE FROM T_TempDB.dbo.T_Logs_history WHERE OperationTime <= DATEADD(M,-1,GETDATE())GODELETE FROM T_TempDB.dbo.T_IPRequests_history WHERE AddTime <= DATEADD(M,-1,GETDATE())GO