# MsSQL 建立Partition Table
###### tags: `MSSQL` , `連猴子也能懂系列`
>來自Jason大大的教學 :+1:
以下步驟以==Record.CoreBet==當切割範例
---
1.建立資料檔及FileGroup
===
1.1 建立6-8個資料檔(.ndf)
---
都先指向同一個file group
``` SQL=
--建立檔案群組
ALTER DATABASE SitePackageRecord
ADD FILEGROUP SitePackageRecord_group1;
GO
--將新的資料檔加入FileGroup
ALTER DATABASE SitePackageRecord
ADD FILE
(
NAME = SitePackageRecord_group1_1,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group1_1.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
),
(
NAME = SitePackageRecord_group1_2,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group1_2.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
),
(
NAME = SitePackageRecord_group1_3,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group1_3.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
),
(
NAME = SitePackageRecord_group1_4,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group1_4.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
),
(
NAME = SitePackageRecord_group1_5,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group1_5.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
),
(
NAME = SitePackageRecord_group1_6,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group1_6.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
),
(
NAME = SitePackageRecord_group1_7,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group1_7.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
),
(
NAME = SitePackageRecord_group1_8,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group1_8.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
)
TO FILEGROUP SitePackageRecord_group1;
GO
```
1.2 建立放置舊資料的資料檔(.ndf)
----
``` sql=+
--建立檔案群組(舊檔)
ALTER DATABASE SitePackageRecord
ADD FILEGROUP SitePackageRecord_group_bak;
GO
--將新的資料檔加入FileGroup(舊檔)
ALTER DATABASE SitePackageRecord
ADD FILE
(
NAME = SitePackageRecord_group_bak,
FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\SitePackageRecord_group_bak.ndf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 200MB
)
TO FILEGROUP SitePackageRecord_group_bak;
GO
```
---
1.3 線上環境預先加大資料檔(視情況而定)
---
>來自Carey大大建議
目的:資料檔Size依線上需要放大一點, 可以減少系統IO需求
```sql=
-- 修改 db file size
ALTER DATABASE SitePackageRecord MODIFY FILE ( NAME ='SitePackageRecord_group1_1', SIZE = 1TB )
ALTER DATABASE SitePackageRecord MODIFY FILE ( NAME ='SitePackageRecord_group1_2', SIZE = 1TB )
ALTER DATABASE SitePackageRecord MODIFY FILE ( NAME ='SitePackageRecord_group1_3', SIZE = 1TB )
ALTER DATABASE SitePackageRecord MODIFY FILE ( NAME ='SitePackageRecord_group1_4', SIZE = 1TB )
ALTER DATABASE SitePackageRecord MODIFY FILE ( NAME ='SitePackageRecord_group1_5', SIZE = 1TB )
ALTER DATABASE SitePackageRecord MODIFY FILE ( NAME ='SitePackageRecord_group1_6', SIZE = 1TB )
ALTER DATABASE SitePackageRecord MODIFY FILE ( NAME ='SitePackageRecord_group1_7', SIZE = 1TB )
ALTER DATABASE SitePackageRecord MODIFY FILE ( NAME ='SitePackageRecord_group1_8', SIZE = 1TB )
GO
```
2.建立輔助函式 Function(非必要)
===
>這步驟非必要, 但方便以後查詢使用
2.1 建立輔助函式 PartitionInfo
---
```sql=
-- ----------------------
-- 建立 PartitionInfo 函式
-- ----------------------
-- 目的: 列出傳入 Table名稱 的各 Partition 的切割臨界值 / 所佔Size / 資料筆數
-- 呼叫範例:
-- SELECT * FROM fn_PartitionInfo('CoreBet');
CREATE FUNCTION fn_PartitionInfo( @tablename sysname ) RETURNS table
AS RETURN
SELECT
OBJECT_NAME(p.object_id) as TableName
,p.partition_number as PartitionNumber
,prv_left.value as LowerBoundary
,prv_right.value as UpperBoundary
,ps.name as PartitionScheme
,pf.name as PartitionFunction
,fg.name as FileGroupName
,CAST(p.used_page_count * 8.0 / 1024 AS NUMERIC(18,2)) AS UsedPages_MB
,p.row_count as Rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
LEFT JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE
p.object_id = OBJECT_ID(@tablename) and p.index_id < 2
GO
```
---
2.2 建立輔助函式 IndexInfo
----
```sql=
-- ----------------------
-- 建立輔助函式 IndexInfo
-- ----------------------
-- 目的: 列出傳入 Table名稱 各個 Index 所在的 File Group 及資料筆數
-- 呼叫範例:
-- SELECT * FROM fn_IndexInfo('CoreBet');
CREATE FUNCTION fn_IndexInfo( @tablename sysname ) RETURNS table
AS RETURN
SELECT
OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
,t.name AS table_name
,i.index_id
,i.name AS index_name
,p.partition_number
,fg.name AS filegroup_name
,FORMAT(p.rows, '#,###') AS rows
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id AND p.partition_number=dds.destination_id
INNER JOIN sys.filegroups fg ON COALESCE(dds.data_space_id, i.data_space_id)=fg.data_space_id
WHERE t.name = @tablename
GO
```
---
3.建立分割函式（Partition Function）
===
這個範例是先切預設三刀, 也可以只切最舊的那一個當預設值
VALUES ('2020-12-01 00:00:00 +00:00');
```sql=
--DROP PARTITION FUNCTION pf_SplitByHour --移除
--GO
CREATE PARTITION FUNCTION pf_SplitByHour(datetimeoffset(0))
AS RANGE RIGHT
FOR VALUES ('2020-12-01 00:00:00 +00:00', '2020-12-01 01:00:00 +00:00', '2020-12-01 02:00:00 +00:00');
GO
```
---
4.建立分割配置 ( Partition Scheme )
===
針對步驟3切三刀, 指派檔案群組[n+1] :4個
這裡需要注意的是, 預計第一個參數group會放最舊的資料, 所以跟其他的group不同
```sql=
--DROP PARTITION SCHEME ps_SplitByHourScheme --移除
--GO
CREATE PARTITION SCHEME ps_SplitByHourScheme
AS PARTITION pf_SplitByHour TO (SitePackageRecord_group_bak, SitePackageRecord_group1, SitePackageRecord_group1, SitePackageRecord_group1);
GO
```
---
5.調整原本的Table Index
===
>由於這是初新者範例, 所以先使用7.切割精靈產生Script, 熟門熟路後參考Carey大大提供的做法:
>1. 新的表，第 5. 和第 7. 的 index 建立動作直接合併一個操作就行
>2. 從已存在單表變 PT ，則建議 DROP INDEX, 再直接執行第 7. 的建立動作就好
>
>==sql server 就可以不用 "建立索引兩次"==
目的: 讓原本的Index都加上主要切割欄位(CreateDateTime), 之後才有辦法加速查詢。
程式應用上也需要調整, 有關這個Table的==where語法都建議加上這個切割欄位==(CreateDateTime)
5.1 PK值變成複合式(TransNumber+CreateDateTime) & 叢集索引
---
``` sql=
--判斷是否存在
IF EXISTS(SELECT 1 FROM sys.sysindexes WHERE name='PK_CoreMemberBet')
ALTER TABLE Corebet DROP CONSTRAINT PK_CoreMemberBet;
GO
--建立新的PK值
ALTER TABLE Corebet ADD CONSTRAINT [PK_CoreBet] PRIMARY KEY CLUSTERED
(
[TransNumber] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
```
5.2 所有Index都變成複合式(+CreateDateTime)
---
```sql=
-- IX_CoreBet_CommonGameCategoryID
IF EXISTS(SELECT 1 FROM sys.sysindexes WHERE name='IX_CoreBet_CommonGameCategoryID')
DROP INDEX CoreBet.IX_CoreBet_CommonGameCategoryID;
GO
CREATE NONCLUSTERED INDEX [IX_CoreBet_CommonGameCategoryID] ON [dbo].[CoreBet]
(
[CommonGameCategoryID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- IX_CoreBet_MemberID
IF EXISTS(SELECT 1 FROM sys.sysindexes WHERE name='IX_CoreBet_MemberID')
DROP INDEX CoreBet.IX_CoreBet_MemberID;
GO
CREATE NONCLUSTERED INDEX [IX_CoreBet_MemberID] ON [dbo].[CoreBet]
(
[MemberID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- IX_CoreBet_PlatformUpdateDateTime
IF EXISTS(SELECT 1 FROM sys.sysindexes WHERE name='IX_CoreBet_PlatformUpdateDateTime')
DROP INDEX CoreBet.IX_CoreBet_PlatformUpdateDateTime;
GO
CREATE NONCLUSTERED INDEX [IX_CoreBet_PlatformUpdateDateTime] ON [dbo].[CoreBet]
(
[PlatformUpdateDateTime] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- IX_CoreBet_WebMasterID
IF EXISTS(SELECT 1 FROM sys.sysindexes WHERE name='IX_CoreBet_WebMasterID')
DROP INDEX CoreBet.IX_CoreBet_WebMasterID;
GO
CREATE NONCLUSTERED INDEX [IX_CoreBet_WebMasterID] ON [dbo].[CoreBet]
(
[WebMasterID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
```
---
6.建立HistoryTable
===
這個新建的History Table 的Index的檔案群組都指向 **放置舊資料的群組[SitePackageRecord_group_bak]**, 之後才可以做Switch。
(Switch需要兩個Table相同群組才能運作)
要注意這個Table裡面的==Index格式需要和來源的Table一致==，不然switch出錯誤訊息。
``` sql=
USE [SitePackageRecord]
GO
/****** Object: Table [dbo].[CoreBetHistory] Script Date: 2020/12/8 下午 05:49:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CoreBetHistory](
[TransNumber] [char](53) NOT NULL,
[InnerBetID] [char](50) NOT NULL,
[CommonGameMerchantID] [smallint] NOT NULL,
[CommonGamePlatformID] [smallint] NOT NULL,
[CommonGameCategoryID] [smallint] NOT NULL,
[BetID] [char](50) NOT NULL,
[WebMasterID] [int] NOT NULL,
[ParentMemberID] [int] NOT NULL,
[MemberID] [int] NOT NULL,
[BetDateTime] [datetimeoffset](0) NOT NULL,
[BetAmount] [decimal](18, 6) NOT NULL,
[ValidBetAmount] [decimal](18, 6) NOT NULL,
[WinLossAmount] [decimal](18, 6) NOT NULL,
[State] [smallint] NOT NULL,
[PlatformUpdateDateTime] [datetimeoffset](7) NOT NULL,
[CreateDateTime] [datetimeoffset](0) NOT NULL,
CONSTRAINT [PK_CoreBetHistory] PRIMARY KEY CLUSTERED
(
[TransNumber] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SitePackageRecord_group_bak]
) ON [SitePackageRecord_group_bak]
GO
/****** Object: Index [IX_CoreBetHistory_CommonGameCategoryID] Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreBetHistory_CommonGameCategoryID] ON [dbo].[CoreBetHistory]
(
[CommonGameCategoryID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SitePackageRecord_group_bak]
GO
/****** Object: Index [IX_CoreBetHistory_CreateDateTime] Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreBetHistory_CreateDateTime] ON [dbo].[CoreBetHistory]
(
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SitePackageRecord_group_bak]
GO
/****** Object: Index [IX_CoreBetHistory_MemberID] Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreBetHistory_MemberID] ON [dbo].[CoreBetHistory]
(
[MemberID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SitePackageRecord_group_bak]
GO
/****** Object: Index [IX_CoreBetHistory_PlatformUpdateDateTime] Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreBetHistory_PlatformUpdateDateTime] ON [dbo].[CoreBetHistory]
(
[PlatformUpdateDateTime] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SitePackageRecord_group_bak]
GO
/****** Object: Index [IX_CoreBetHistory_WebMasterID] Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreBetHistory_WebMasterID] ON [dbo].[CoreBetHistory]
(
[WebMasterID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SitePackageRecord_group_bak]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_CoreBetHistoryOrder_InnerBetID] Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreBetHistoryOrder_InnerBetID] ON [dbo].[CoreBetHistory]
(
[InnerBetID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SitePackageRecord_group_bak]
GO
ALTER TABLE [dbo].[CoreBetHistory] ADD CONSTRAINT [DF_CoreBetHistory_CreateDateTime] DEFAULT (getutcdate()) FOR [CreateDateTime]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易序號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'TransNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'內部下注單號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'InnerBetID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統遊戲商ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'CommonGameMerchantID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統遊戲平台ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'CommonGamePlatformID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統遊戲類型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'CommonGameCategoryID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注單ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'BetID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'站長ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'WebMasterID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父會員ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'ParentMemberID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'會員ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'MemberID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'下注時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'BetDateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注單更新時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CoreBetHistory', @level2type=N'COLUMN',@level2name=N'PlatformUpdateDateTime'
GO
```
---
7.將Table與Partition function/scheme 連結
===
>可以由切割精靈產生。
>
步驟: [Table名稱]按右鍵>儲存體>建立分割區，並選擇先前建立好的分割函數和配置。
下圖分割精靈的這個選項==建議打勾==, 讓index 重整一次
```sql=
USE [SitePackageRecord]
GO
BEGIN TRANSACTION
ALTER TABLE [dbo].[CoreBet] DROP CONSTRAINT [PK_CoreBet] WITH ( ONLINE = OFF )
SET ANSI_PADDING ON
ALTER TABLE [dbo].[CoreBet] ADD CONSTRAINT [PK_CoreBet] PRIMARY KEY CLUSTERED
(
[TransNumber] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_SplitByHourScheme]([CreateDateTime])
CREATE NONCLUSTERED INDEX [IX_CoreBet_CommonGameCategoryID] ON [dbo].[CoreBet]
(
[CommonGameCategoryID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_SplitByHourScheme]([CreateDateTime])
CREATE NONCLUSTERED INDEX [IX_CoreBet_MemberID] ON [dbo].[CoreBet]
(
[MemberID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_SplitByHourScheme]([CreateDateTime])
CREATE NONCLUSTERED INDEX [IX_CoreBet_PlatformUpdateDateTime] ON [dbo].[CoreBet]
(
[PlatformUpdateDateTime] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_SplitByHourScheme]([CreateDateTime])
CREATE NONCLUSTERED INDEX [IX_CoreBet_WebMasterID] ON [dbo].[CoreBet]
(
[WebMasterID] ASC,
[CreateDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_SplitByHourScheme]([CreateDateTime])
COMMIT TRANSACTION
```
執行完上方script後, 可以使用先前建立的function查詢partition結果。
由於範例是先切三刀, 所以會有3段日期區段的資料
```sql=
SELECT * FROM fn_PartitionInfo('corebet');
```
---
8.使用SQL Agent定期建立新Partition/合併舊的Partition
===
- SQL Agent 設定每小時執行一次
- PT: Partition Table縮寫
流程:
```flow
st=>start: 開始
(每小時啟動一次)
e=>end: 結束
cond1=>condition: 需要補建
PT時段?
cond2=>condition: 有超過32天的
PT時段?
s1=>subroutine: 建立新時段PT
(兩天份)
s2=>subroutine: 合併PT時段到
存放舊資料PT
s3=>operation: 將舊資料PT switch
到History Table
st->cond1
cond1(no,left)->cond2
cond1(yes, right)->s1->cond2
cond2(yes, right)->s2->s3
cond2(no,left)->s3
s3->e
```
---
8.1 預先切割2天份Partition
---
```sql=
-----先往後切2天份Partition----
DECLARE @MaxDate datetimeoffset
DECLARE @NowDate datetimeoffset = getutcdate()
--預先建立兩天份的Partition
DECLARE @CalculationHourCount INT = 48 --單位:小時
---- 取出目前Partition裡日期最大的一個
SELECT @MaxDate=CAST(MAX([VALUE]) AS datetimeoffset)
FROM SYS.PARTITION_FUNCTIONS pf INNER JOIN SYS.PARTITION_RANGE_VALUES pr ON pf.function_id = pr.function_id
WHERE [name] = 'pf_SplitByHour'
--目前日期 與 +n hour 判斷, 是否需要補建Partition
DECLARE @HourCount INT
SET @HourCount = DATEDIFF(HOUR,@MaxDate, DATEADD(HOUR, @CalculationHourCount, @NowDate))
PRINT (@HourCount)
WHILE @HourCount > 0
BEGIN
SET @MaxDate = DATEADD(HOUR, 1, @MaxDate)
PRINT (@MaxDate)
--Partition新的時間區段
ALTER PARTITION SCHEME ps_SplitByHourScheme NEXT USED [SitePackageRecord_group1]
ALTER PARTITION FUNCTION pf_SplitByHour() SPLIT RANGE (@MaxDate)
--next value
SET @HourCount -= 1
END
```
8.2 合併32天後Partition
---
```sql=+
-----合併32天後Partition----
DECLARE @MinDate datetimeoffset
DECLARE @NowDate datetimeoffset = getutcdate()
--合併幾天後的Partition區段
DECLARE @CalculationHourCount INT = 768 -- 小時(32天 * 24h = 768h)
---- 取出目前Partition裡日期最小的一個
SELECT @MinDate=CAST(min([VALUE]) AS datetimeoffset)
FROM SYS.PARTITION_FUNCTIONS pf INNER JOIN SYS.PARTITION_RANGE_VALUES pr ON pf.function_id = pr.function_id
WHERE [name] = 'pf_SplitByHour'
DECLARE @HourCount INT
SET @HourCount =DATEDIFF(HOUR, @MinDate, DATEADD(HOUR, 0, @NowDate))
PRINT @HourCount
WHILE @HourCount > @CalculationHourCount
BEGIN
PRINT @MinDate
--將舊資料合併回bak group
ALTER PARTITION FUNCTION pf_SplitByHour ()
MERGE RANGE (@MinDate);
--next value
SET @MinDate = DATEADD(HOUR, 1, @MinDate)
SET @HourCount = DATEDIFF(HOUR, @MinDate, @NowDate)
END
```
8.3 將舊資料Switch 到History Table (Partition Switch)
---
>感謝Carey大大提點
將資料switch到 History Table, 之後的搬帳動作(Move to History DB)就只要針對這個Histroy Table 去做搬移就好了。
這樣移動方式就不會造成 CoreBet 的 Table Lock :+1:
```sql=
USE [SitePackageRecord]
GO
-- 將舊資料PT Switch 到 CoreBetHistory
ALTER TABLE Corebet SWITCH PARTITION 1 TO CorebetHistory WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE));
GO
```
上面的指令會把 PT:1 的26筆資料都switch到 CorebetHistory 中
9.使用Cursor方式Switch多張Table
```sql=
DECLARE @TableName VARCHAR(100) --table 名稱
--建立Cursor, 取得需要Switch Table 清單
DECLARE TableCursor CURSOR FORWARD_ONLY FOR
SELECT TableName
FROM MoveToHistoryTableName
--逐筆run一次
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName; --擷取游標內容
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'table name: ' + @TableName;
--(do something)
--next
FETCH NEXT FROM TableCursor INTO @TableName;
END;
--close cursor
CLOSE TableCursor;
DEALLOCATE TableCursor;
```
Sign in with Wallet
Connect another wallet