sqlserver table partion

標籤：code   images   str   har   準備工作   ref   tle   insert   測試表   

1. 概要說明

SQL SERVER的表資料分割函數是為了將一個大表（表中含有非常多條資料）的資料根據某條件（僅限該表的主鍵）拆分成多個檔案存放，以提高查詢資料時的效率。建立表分區的主要步驟是1、確定需要以哪一個欄位作為分區條件；2、拆分成多少個檔案儲存該表；3、分區函數（拆分條件）；4、資料分割配置（按拆分函數拆分後需要對應到哪些檔案組中去）。

下面就一步一步來說明如何建立表分區：

2. 準備工作

建立一個測試表

CREATE TABLE Sale(      [Id] [int] IDENTITY(1,1) NOT NULL,          --自動成長      [Name] [varchar](16) NOT NULL,      [SaleTime] [datetime] NOT NULL,      CONSTRAINT [PK_Sale] PRIMARY KEY CLUSTERED  --建立主鍵      (          [Id] ASC      )  )  

插入測試資料

insert Sale ([Name],[SaleTime]) values (‘張三‘,‘2009-1-1‘)    insert Sale ([Name],[SaleTime]) values (‘李四‘,‘2009-2-1‘)    insert Sale ([Name],[SaleTime]) values (‘王五‘,‘2009-3-1‘)    insert Sale ([Name],[SaleTime]) values (‘錢六‘,‘2012-4-1‘)    insert Sale ([Name],[SaleTime]) values (‘趙七‘,‘2012-6-1‘)    insert Sale ([Name],[SaleTime]) values (‘張三‘,‘2012-6-1‘)    insert Sale ([Name],[SaleTime]) values (‘李四‘,‘2012-7-1‘)    insert Sale ([Name],[SaleTime]) values (‘王五‘,‘2012-8-1‘)    insert Sale ([Name],[SaleTime]) values (‘錢六‘,‘2012-10-1‘)    insert Sale ([Name],[SaleTime]) values (‘趙七‘,‘2012-10-1‘)    insert Sale ([Name],[SaleTime]) values (‘張三‘,‘2012-11-1‘)    insert Sale ([Name],[SaleTime]) values (‘李四‘,‘2013-12-1‘)    insert Sale ([Name],[SaleTime]) values (‘王五‘,‘2014-12-1‘)  

 

3. 實現步驟

主鍵設定

表分區需要先確定一個欄位，按此欄位的某個條件進行拆分，我們這裡以Saletime列為例，按月為單位對Sale表進行拆分。因為需要拆分的列必須是主鍵，所以我們這裡先刪除原來建表時對ID欄位建立的主鍵，改為SaleTime欄位（注意用非聚集主鍵）

1）修改表

 

2）移除主鍵

 

3）建立主鍵

 

4）設定關聯

 

因為主鍵要求唯一性，所以這裡需要做2個欄位的關聯主鍵（ID與SaleTime）

 

5）修改主鍵為非聚集

 

成功後的效果

 

完成後記得儲存表

建立檔案組和資料檔案

因為表分區時按照檔案組為單位儲存了，而實際資料是儲存在這個檔案組所包含的檔案中的，所以為了高效率，可以一個檔案組對應一個資料檔案來儲存資料，下面我們以月為單位建立檔案組

 

到這裡已經把檔案組和資料檔案建立完畢並建立了對應關係，點擊確定鍵後，在對應的目錄下可以看到已經產生了我們設定的12個資料檔案了。

 

建立分區函數（無法可視化實現）

-- 第四步 建立分區函數CREATE PARTITION FUNCTION partfunSale (DATETIME)   AS RANGE RIGHT FOR VALUES (     ‘2012-02-01‘,‘2012-03-01‘,‘2012-04-01‘,‘2012-05-01‘,‘2012-06-01‘,     ‘2012-07-01‘,‘2012-08-01‘,‘2012-09-01‘,‘2012-10-01‘,‘2012-11-01‘,‘2012-12-01‘)

上面這段的含義是建立一個以Datetime欄位類型的分區函數，需要注意的是12個檔案組對應11個Values，因為分區的規則是

檔案組1 ———》2012-02-01之前的資料（日期>2012-02-01）
檔案組2 ———》2012-02-01之後2012-03-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組3 ———》2012-03-01之後2012-04-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組4 ———》2012-04-01之後2012-05-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組5 ———》2012-05-01之後2012-06-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組6 ———》2012-06-01之後2012-07-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組7 ———》2012-07-01之後2012-08-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組8 ———》2012-08-01之後2012-09-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組9 ———》2012-09-01之後2012-10-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組10 ———》2012-10-01之後2012-11-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組11 ———》2012-11-01之後2012-12-01之前的資料（2012-02-01≤日期＜2012-03-01）
檔案組12 ———》2012-12-01之後的資料（2012-02-01≤日期＜2012-03-01）

 建立資料分割配置（無法可視化實現）

將建立的分區函數與檔案組進行關聯

-- 第四步 建立資料分割配置(注意要比分區函數多一項）CREATE PARTITION SCHEME partschSale   AS PARTITION partfunSale   TO (          Saletime201201,     Saletime201202,       Saletime201203,       Saletime201204,       Saletime201205,       Saletime201206,       Saletime201207,       Saletime201208,     Saletime201209,       Saletime201210,       Saletime201211,       Saletime201212    )

關聯到表（無法可視化實現）

將設定好的資料分割配置與具體的表進行關聯

-- 第五步 設定資料分割配置到指定表CREATE CLUSTERED INDEX CT_Sale ON Sale([SaleTime])  ON partschSale([SaleTime]) 

其中Sale是表名，SaleTime是拆分時依據的欄位，partschSale是資料分割配置

建立好了以後在資料庫中右鍵表名點擊屬性可以查看到類似如下的效果表示建立成功

統計各資料群組中包含的資料條數

-- 統計所有分區表中的記錄總數   select $PARTITION.partfunSale(SaleTime) as 分區編號,count(id) as 記錄數from Sale group by $PARTITION.partfunSale(SaleTime)  

 

sqlserver table partion