---
# System prepended metadata

title: MsSQL 建立Partition Table
tags: [MSSQL, 連猴子也能懂系列]

---

# MsSQL 建立Partition Table
###### tags: `MSSQL` , `連猴子也能懂系列`

>來自Jason大大的教學 :+1:

 
以下步驟以==Record.CoreData==當切割範例

 
---
1.建立資料檔及FileGroup 
===

1.1 建立6-8個資料檔(.ndf)
---
都先指向同一個file group

```  SQL=

--建立檔案群組
ALTER DATABASE Record
ADD FILEGROUP Record_group1;
GO
--將新的資料檔加入FileGroup
ALTER DATABASE Record
ADD FILE
(
    NAME = Record_group1_1,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group1_1.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
),
(
    NAME = Record_group1_2,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group1_2.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
),
(
    NAME = Record_group1_3,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group1_3.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
),
(
    NAME = Record_group1_4,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group1_4.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
),
(
    NAME = Record_group1_5,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group1_5.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
),
(
    NAME = Record_group1_6,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group1_6.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
),
(
    NAME = Record_group1_7,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group1_7.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
),
(
    NAME = Record_group1_8,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group1_8.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
)
TO FILEGROUP Record_group1;
GO

```
1.2 建立放置舊資料的資料檔(.ndf)
----
``` sql=+
--建立檔案群組(舊檔)
ALTER DATABASE Record
ADD FILEGROUP Record_group_bak;
GO
--將新的資料檔加入FileGroup(舊檔)
ALTER DATABASE Record
ADD FILE
(
    NAME = Record_group_bak,
    FILENAME = 'D:\ProgramFiles\MSSQL14.MSSQLSERVER_2017\MSSQL\DATA\Record_group_bak.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 200MB
)
TO FILEGROUP Record_group_bak;
GO

```
  
    
---


1.3 線上環境預先加大資料檔(視情況而定)
---
>來自Carey大大建議

目的:資料檔Size依線上需要放大一點, 可以減少系統IO需求

```sql=

-- 修改 db file size
ALTER DATABASE Record MODIFY FILE ( NAME ='Record_group1_1', SIZE = 1TB )
ALTER DATABASE Record MODIFY FILE ( NAME ='Record_group1_2', SIZE = 1TB )
ALTER DATABASE Record MODIFY FILE ( NAME ='Record_group1_3', SIZE = 1TB )
ALTER DATABASE Record MODIFY FILE ( NAME ='Record_group1_4', SIZE = 1TB )
ALTER DATABASE Record MODIFY FILE ( NAME ='Record_group1_5', SIZE = 1TB )
ALTER DATABASE Record MODIFY FILE ( NAME ='Record_group1_6', SIZE = 1TB )
ALTER DATABASE Record MODIFY FILE ( NAME ='Record_group1_7', SIZE = 1TB )
ALTER DATABASE Record MODIFY FILE ( NAME ='Record_group1_8', SIZE = 1TB )
GO

```

2.建立輔助函式 Function(非必要)
===
>這步驟非必要, 但方便以後查詢使用

2.1 建立輔助函式 PartitionInfo 
---
```sql=
-- ----------------------
-- 建立 PartitionInfo 函式  
-- ----------------------
-- 目的: 列出傳入 Table名稱 的各 Partition 的切割臨界值 / 所佔Size / 資料筆數 
-- 呼叫範例:
-- SELECT * FROM fn_PartitionInfo('CoreData'); 

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('CoreData'); 

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
```

2.3 查詢DB中使用Partition Table的 Cloumn
---
```sql=
select 
OBJECT_NAME(i.object_id), c.name from sys.index_columns i join sys.columns c on i.object_id = c.object_id 
and i.column_id = c.column_id where partition_ordinal = 1
```



---


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 (Record_group_bak, Record_group1, Record_group1, Record_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_CoreData] 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_CoreData_CommonGameCategoryID 
IF EXISTS(SELECT 1 FROM sys.sysindexes  WHERE name='IX_CoreData_CommonGameCategoryID')
	DROP INDEX CoreData.IX_CoreData_CommonGameCategoryID;
GO
CREATE NONCLUSTERED INDEX [IX_CoreData_CommonGameCategoryID] ON [dbo].[CoreData]
(
	[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_CoreData_MemberID
IF EXISTS(SELECT 1 FROM sys.sysindexes  WHERE name='IX_CoreData_MemberID')
	DROP INDEX CoreData.IX_CoreData_MemberID;
GO
CREATE NONCLUSTERED INDEX [IX_CoreData_MemberID] ON [dbo].[CoreData]
(
	[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_CoreData_PlatformUpdateDateTime
IF EXISTS(SELECT 1 FROM sys.sysindexes  WHERE name='IX_CoreData_PlatformUpdateDateTime')
	DROP INDEX CoreData.IX_CoreData_PlatformUpdateDateTime;
GO
CREATE NONCLUSTERED INDEX [IX_CoreData_PlatformUpdateDateTime] ON [dbo].[CoreData]
(
	[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_CoreData_WebMasterID
IF EXISTS(SELECT 1 FROM sys.sysindexes  WHERE name='IX_CoreData_WebMasterID')
	DROP INDEX CoreData.IX_CoreData_WebMasterID;
GO
CREATE NONCLUSTERED INDEX [IX_CoreData_WebMasterID] ON [dbo].[CoreData]
(
	[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的檔案群組都指向 **放置舊資料的群組[Record_group_bak]**, 之後才可以做Switch。
(Switch需要兩個Table相同群組才能運作)

要注意這個Table裡面的==Index格式需要和來源的Table一致==，不然switch出錯誤訊息。


``` sql= 

USE [Record]
GO
/****** Object:  Table [dbo].[CoreDataHistory]    Script Date: 2020/12/8 下午 05:49:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CoreDataHistory](
	[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_CoreDataHistory] 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 [Record_group_bak]
) ON [Record_group_bak]
GO
/****** Object:  Index [IX_CoreDataHistory_CommonGameCategoryID]    Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreDataHistory_CommonGameCategoryID] ON [dbo].[CoreDataHistory]
(
	[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 [Record_group_bak]
GO
/****** Object:  Index [IX_CoreDataHistory_CreateDateTime]    Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreDataHistory_CreateDateTime] ON [dbo].[CoreDataHistory]
(
	[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 [Record_group_bak]
GO
/****** Object:  Index [IX_CoreDataHistory_MemberID]    Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreDataHistory_MemberID] ON [dbo].[CoreDataHistory]
(
	[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 [Record_group_bak]
GO
/****** Object:  Index [IX_CoreDataHistory_PlatformUpdateDateTime]    Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreDataHistory_PlatformUpdateDateTime] ON [dbo].[CoreDataHistory]
(
	[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 [Record_group_bak]
GO
/****** Object:  Index [IX_CoreDataHistory_WebMasterID]    Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreDataHistory_WebMasterID] ON [dbo].[CoreDataHistory]
(
	[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 [Record_group_bak]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IX_CoreDataHistoryOrder_InnerBetID]    Script Date: 2020/12/8 下午 05:49:05 ******/
CREATE NONCLUSTERED INDEX [IX_CoreDataHistoryOrder_InnerBetID] ON [dbo].[CoreDataHistory]
(
	[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 [Record_group_bak]
GO
ALTER TABLE [dbo].[CoreDataHistory] ADD  CONSTRAINT [DF_CoreDataHistory_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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @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'CoreDataHistory', @level2type=N'COLUMN',@level2name=N'PlatformUpdateDateTime'
GO



```




---
7.將Table與Partition function/scheme 連結
===
>可以由切割精靈產生。
>
步驟: [Table名稱]按右鍵>儲存體>建立分割區，並選擇先前建立好的分割函數和配置。

下圖分割精靈的這個選項==建議打勾==, 讓index 重整一次
![](https://i.imgur.com/VFntEy2.png)



```sql=

USE [Record]
GO
BEGIN TRANSACTION

ALTER TABLE [dbo].[CoreData] DROP CONSTRAINT [PK_CoreData] WITH ( ONLINE = OFF )

SET ANSI_PADDING ON

ALTER TABLE [dbo].[CoreData] ADD  CONSTRAINT [PK_CoreData] 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_CoreData_CommonGameCategoryID] ON [dbo].[CoreData]
(
	[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_CoreData_MemberID] ON [dbo].[CoreData]
(
	[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_CoreData_PlatformUpdateDateTime] ON [dbo].[CoreData]
(
	[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_CoreData_WebMasterID] ON [dbo].[CoreData]
(
	[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');
```
![](https://i.imgur.com/akfxUeq.png)



---
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 [Record_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 去做搬移就好了。

這樣移動方式就不會造成 CoreData 的 Table Lock :+1: 


```sql=

USE [Record]
GO

 -- 將舊資料PT Switch 到 CoreDataHistory
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 中
![123](https://i.imgur.com/bZgaume.png)

