一.1  確定表是否分割槽
如果表 PartitionTable 已分割槽，以下查詢將返回一個或多個行。 如果表未分割槽，則不返回任何行。
SELECT *   
FROM sys.tables AS t   
JOIN sys.indexes AS i   
    ON t.[object_id] = i.[object_id]   
    AND i.[type] IN (0,1)   
JOIN sys.partition_schemes ps   
    ON i.data_space_id = ps.data_space_id   
WHERE t.name = 'PartitionTable';   
或：
SELECT ps.*
  FROM sys.indexes i
  JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id
 WHERE i.object_id = object_id('PartitionTable') 
 
一.2  確定已分割槽表的邊界值
以下查詢對於 PartitionTable 表中的每個分割槽返回邊界值。
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f   
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r   
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE t.name = 'PartitionTable' AND i.type <= 1  
ORDER BY p.partition_number;  
一.3  確定已分割槽表的分割槽列
以下查詢返回表的分割槽列的名稱。 PartitionTable中建立已分割槽表或索引。
SELECT   
    t.[object_id] AS ObjectID   
    , t.name AS TableName   
    , ic.column_id AS PartitioningColumnID   
    , c.name AS PartitioningColumnName   
FROM sys.tables AS t   
JOIN sys.indexes AS i   
    ON t.[object_id] = i.[object_id]   
    AND i.[type] <= 1 -- clustered index or a heap   
JOIN sys.partition_schemes AS ps   
    ON ps.data_space_id = i.data_space_id   
JOIN sys.index_columns AS ic   
    ON ic.[object_id] = i.[object_id]   
    AND ic.index_id = i.index_id   
    AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column   
JOIN sys.columns AS c   
    ON t.[object_id] = c.[object_id]   
    AND ic.column_id = c.column_id   
WHERE t.name = 'PartitionTable' ;   
 
一.4  其他
 
select * from sys.filegroups; -- 檔案組
SELECT * FROM sys.partition_functions; -- 分割槽函式
SELECT * FROM sys.partition_schemes; -- 分割槽方案
select $partition.Function_DateTime('2012-02-01');
select $partition.Function_DateTime('2011-01-01');
 
一.5  所有分割槽表
 
 
SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
      CASE pf.boundary_value_on_right
            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber;
 
 
一.6  將一個普通錶轉換成一個分割槽表
如何將一個普通錶轉換成一個分割槽表呢？說到底，只要將該表建立一個聚集索引，並在聚集索引上使用分割槽方案即可。
 
1、刪除普通表中的聚集索引
--刪掉主鍵  
ALTER TABLE testTab DROP constraint PK_testTab  
--建立主鍵，但不設為聚集索引  
ALTER TABLE testTab ADD CONSTRAINT PK_testTab  PRIMARY KEY NONCLUSTERED  
(  
    [ID] ASC  
) ON [PRIMARY] 
2、建立一個分割槽函式
CREATE PARTITION FUNCTION part_month_func_range_test(datetime)  
AS RANGE RIGHT FOR VALUES (
'2017-7-1 00:00:00',
'2017-8-1 00:00:00',
'2017-8-1 00:00:00',
'2017-9-1 00:00:00',
'2017-10-1 00:00:00',
'2017-11-1 00:00:00',
);
3、建立一個分割槽方案
CREATE PARTITION SCHEME partschSale  
AS PARTITION part_month_func_range_test
TO (  
  FC201706,  
  FC201707,  
  FC201708, 
  FC201709,  
  FC201710，
  FC201711，
  FC201712
) ;
4、按分割槽方案建立聚集索引
 
--建立一個新的聚集索引，在該聚集索引中使用分割槽方案  
CREATE CLUSTERED INDEX CT_testTab ON Sale([inDate])  
ON part_month_func_range_test([inDate]);  
 
為表建立了一個使用分割槽方案的聚集索引之後，該普通表就變成了一個分割槽表了。

1. 分割槽表簡介

　　分割槽表在邏輯上是一個表，而物理上是多個表。從使用者角度來看，分割槽表和普通表是一樣的。使用分割槽表的主要目的是為改善大型表以及具有多個訪問模式的表的可伸縮性和可管理性。

　　分割槽表是把資料按設定的標準劃分成區域儲存在不同的檔案組中，使用分割槽可以快速而有效管理和訪問資料子集。

1.1> 適合做分割槽表的情況

　　◊ 資料庫中某個表的資料很多，在查詢資料時會明顯感覺到速度很慢，這個時候需要考慮分割槽表；

　　◊ 資料是分段的，如以年份為分隔的資料，對於當年的資料經常進行增刪改查操作，而對於往年的資料幾乎不做操作或只做查詢操作，這種情況可以使用分割槽表。對資料的操作如果只涉及一部分資料而不是全部資料的情況可以考慮分割槽表，如果一張表的資料經常使用且不管年份之類的因素經常對其增刪改查操作則最好不要分割槽。

1.2> 分割槽表的優點

　　◊ 分割槽表可以從物理上將一個大表分成幾個小表，但是從邏輯上來看還是一個大表。

　　◊ 對於具有多個CPU的系統，分割槽可以對錶的操作透過並行的方式進行，可以提升訪問效能。

SQL Server   支援表和索引分割槽。   已分割槽表和已分割槽索引的資料劃分為分佈於一個資料庫中多個檔案組的單元。   資料是按水平方式分割槽的，因此多組行對映到單個的分割槽。   單個索引或表的所有分割槽都必須位於同一個資料庫中。   對資料進行查詢或更新時，表或索引將被視為單個邏輯實體。   在  Microsoft SQL Server  的各版本中均不提供已分割槽的表和索引。   有關的各版本支援的功能列表 SQL Server ，請參閱 SQL Server 2014 各個版本支援的功能 。

分割槽的優點

透過對大型表或索引進行分割槽，可以具有以下可管理性和效能優點。

• 可以快速、高效地傳輸或訪問資料的子集，同時又能維護資料收集的完整性。   例如，將資料從 OLTP 載入到 OLAP 系統之類的操作僅需幾秒鐘即可完成，而如果不對資料進行分割槽，執行此操作需要幾分鐘或幾小時。

• 您可以更快地對一個或多個分割槽執行維護操作。   這些操作的效率更高，因為它們僅針對這些資料子集，而非整個表。 例如，您可以選擇在一個或多個分割槽中壓縮資料，或者重新生成索引的一個或多個分割槽。

• 您可以根據經常執行的查詢型別和硬體配置，提高查詢效能。   例如，在兩個或更多的已分割槽表中的分割槽列相同時，查詢最佳化器可以更快地處理這些表之間的同等聯接查詢，因為可以聯接這些分割槽本身。

  當  SQL Server  針對 I/O 操作執行資料排序時，它會首先按分割槽對資料進行排序。   SQL Server   每次訪問一個驅動器，這樣可能會降低效能。   為了提高資料排序效能，可以透過設定 RAID 將多個磁碟中的分割槽資料檔案條帶化。   這樣一來，儘管  SQL Server  仍按分割槽對資料進行排序，但它可以同時訪問每個分割槽的所有驅動器。

  此外，您可以透過對在分割槽級別而不是整個表啟用鎖升級來提高效能。   這可以減少表上的鎖爭用。

元件和概念

以下術語適用於表和索引分割槽。

分割槽函式
一種資料庫物件，它定義如何根據某個列（稱為分割槽列）的值將表或索引的行對映到一組分割槽。   也就是說，分割槽函式定義表將具有的分割槽數和分割槽邊界的定義方式。   例如，假定一個包含銷售訂單資料的表，您可能需要基於  datetime  列（如銷售日期）將表劃分為 12 個（按月）分割槽。

分割槽方案
將分割槽函式的分割槽對映到一組檔案組的資料庫物件。   在各個檔案組上放置分割槽的主要原因是為了確保可以在分割槽上獨立執行備份操作。   這是因為您可以在各個檔案組上執行備份。

分割槽列
分割槽函式對錶或索引進行分割槽時所使用的表或索引列。   參與分割槽函式的計算列必須顯式標記為 PERSISTED。   用作索引列時有效的所有資料型別都可以用作分割槽依據列， timestamp  除外。   無法指定  ntext 、 text 、 image 、 xml 、 varchar(max) 、 nvarchar(max)  或  varbinary(max)  資料型別。   此外，無法指定 Microsoft .NET Framework 公共語言執行時 (CLR) 使用者定義型別和別名資料型別列。

對齊的索引
與其對應的表建立在同一個分割槽方案之上的一種索引。   如果表與其索引對齊，SQL Server 則可以快速高效地切換分割槽，同時又能維護表及其索引的分割槽結構。   索引要與其基表對齊，並不需要與基表參與相同的命名分割槽函式。   但是，索引和基表的分割槽函式在實質上必須相同，即：1) 分割槽函式的引數具有相同的資料型別；2) 分割槽函式定義了相同數目的分割槽；3) 分割槽函式為分割槽定義了相同的邊界值。

非對齊的索引
獨立於其相應的表進行分割槽的一種索引。   也就是說，索引具有不同的分割槽方案或者放置於不同於基表的單獨檔案組中。   在下列情況下，設計非對齊的分割槽索引可能會很有用：

• 基表未分割槽。

• 索引鍵是唯一的，不包含表的分割槽依據列。

• 您希望基表與使用不同聯接列的多個表一起參與並置聯接。

分割槽排除
查詢最佳化器用來僅訪問相關分割槽以便滿足查詢的篩選條件的過程。

效能準則

這個新的、更高的 15,000 個分割槽的限制將影響記憶體、分割槽的索引操作、DBCC 命令和查詢。   本節介紹將分割槽數目增加到超過 1,000 個的效能影響並根據需要提供解決方法。   由於對分割槽最大數目的限制已增加到 15,000 個，因此您可以儲存更長時間的資料。   不過，您應該僅保留所需時長的資料，並且在效能和分割槽數目之間保持平衡。

記憶體使用情況和指導方針

如果正在使用大量分割槽，我們建議您使用至少 16 GB 的 RAM。   如果系統沒有足夠的記憶體，則資料操作語言 (DML) 語句、資料定義語言 (DDL) 語句和其他操作可能會由於記憶體不足而失敗。   如果系統具有 16 GB 的 RAM 並且執行許多大量佔用記憶體的程式，則在執行大量分割槽的操作時，可能會出現記憶體不足的情況。   因此，您具有超過 16 GB 的記憶體越多，您遇到效能和記憶體問題的可能性就越低。

記憶體限制可能會影響 SQL Server 生成已分割槽索引的效能或能力。   如果表中已應用聚集索引，當索引未與其基表或聚集索引對齊時更是如此。

已分割槽索引操作

記憶體限制可能會影響 SQL Server 生成已分割槽索引的效能或能力。   具有非對齊索引的情況尤其是這樣。   對超過 1,000 個分割槽的表建立和重新生成非對齊索引是可能的，但不支援。   這樣做可能會導致效能下降，或在執行這些操作的過程中佔用過多記憶體。

隨著分割槽數目的增加，建立和重新生成對齊索引的執行時間可能會更長。   我們建議您不要同時執行多個建立和重新生成索引命令，因為可能會遇到效能和記憶體問題。

當 SQL Server 執行排序以生成已分割槽索引時，它首先為每個分割槽生成一個排序表。   然後在每個分割槽各自的檔案組中生成排序表，或者在  tempdb  中生成排序表（如果指定了 SORT_IN_TEMPDB 索引選項）。   每個排序表都需要一個最小記憶體量才能生成。   在生成與其基表對齊的已分割槽索引時，將一次生成一個排序表，因此使用的記憶體較少。   但是，在生成非對齊的已分割槽索引時，將同時生成排序表。   因此，必須有足夠的記憶體來處理這些併發的排序。   分割槽數越多，所需的記憶體越多。   每個分割槽的每個排序表的最小大小為 40 頁，每頁 8 KB。   例如，具有 100 個分割槽的非對齊已分割槽索引需要足夠的記憶體才能同時連續地對 4,000 (40 * 100) 頁進行排序。   如果有這麼多的可用記憶體，生成操作將成功，但效能可能會降低。   如果沒有這麼多可用記憶體，生成操作將失敗。   而具有 100 個分割槽的對齊已分割槽索引只需要具有對 40 頁進行排序的記憶體就足夠了，因為不會同時執行排序。

無論是對齊索引還是非對齊索引，如果 SQL Server 對多處理器計算機上的生成操作應用了並行度，需要的記憶體可能會更多。   這是因為並行度越高，需要的記憶體就越多。   例如，如果 SQL Server 將並行度設定為 4，那麼具有 100 個分割槽的非對齊已分割槽索引將需要使四個處理器同時分別對 4,000 頁（即，共 16,000 頁）進行排序的足夠記憶體。   如果已分割槽索引是對齊的，需要的記憶體將減少，只要夠四個處理器分別對 40 頁（共 160 頁，即 4 * 40）進行排序就行了。   您可以使用 MAXDOP 索引選項手動降低並行度。

DBCC 命令

在具有較多分割槽的情況下，隨著分割槽數目的增加，DBCC 命令可能需要更長的時間來執行。

查詢

與具有大量分割槽的查詢相比，使用分割槽排除的查詢在效能上相當或更高。   隨著分割槽數目的增加，未使用分割槽排除的查詢可能需要更長的時間來執行。

例如，假定一個表具有 1 億行和列的  A 、  B 和  C 。   在方案 1 中，該表在列  A 上劃分為 1000 個分割槽。   在方案 2 中，該表在列  A 上劃分為 10,000 個分割槽。   針對該表的一個查詢（該查詢對列  A  具有 WHERE 子句篩選）將執行分割槽排除並且掃描一個分割槽。   同一個查詢在方案 2 中的執行速度可能會更快，因為在分割槽中要掃描的行數更少。   對列 B 具有 WHERE 子句篩選的查詢將掃描所有分割槽。   與在方案 2 中相比，該查詢在方案 1 中的執行速度會更快，因為要掃描更少的分割槽。

在分割槽列之外的其他列上使用運算子（如 TOP 或 MAX/MIN）的查詢可能會遇到分割槽效能降低的情況，因為所有分割槽都必須進行評估。

已分割槽索引操作期間統計資訊計算中的行為更改

從  SQL Server 2012 開始，當建立或重新生成已分割槽索引時，將透過掃描表中的所有行來建立統計資訊。   相反，查詢最佳化器使用預設取樣演算法來生成統計資訊。   在升級具有已分割槽索引的資料庫後，您可以在直方圖資料中注意到針對這些索引的差異。   此行為更改可能不會影響查詢效能。   若要透過掃描表中所有行的方法獲得有關已分割槽索引的統計資訊，請使用 CREATE STATISTICS 或 UPDATE STATISTICS 以及 FULLSCAN 子句。

什麼是表分割槽

一般情況下，我們建立資料庫表時，表資料都存放在一個檔案裡。

但是如果是分割槽表的話，表資料就會按照你指定的規則分放到不同的檔案裡，把一個大的資料檔案拆分為多個小檔案，還可以把這些小檔案放在不同的磁碟下由多個cpu進行處理。這樣檔案的大小隨著拆分而減小，還得到硬體系統的加強，自然對我們運算元據是大大有利的。

所以大資料量的資料表，對分割槽的需要還是必要的，因為它可以提高select效率，還可以對歷史資料經行區分存檔等。但是資料量少的資料就不要湊這個熱鬧啦，因為表分割槽會對資料庫產生不必要的開銷，除啦效能還會增加實現物件的管理費用和複雜性。

確定表是否分割槽

1. 如果表  PartitionTable  已分割槽，以下查詢將返回一個或多個行。   如果表未分割槽，則不返回任何行。

   
   

   SELECT *   
   FROM sys.tables AS t   
   JOIN sys.indexes AS i   
       ON t.[object_id] = i.[object_id]   
       AND i.[type] IN (0,1)   
   JOIN sys.partition_schemes ps   
       ON i.data_space_id = ps.data_space_id   
   WHERE t.name = 'PartitionTable';   
   GO
   

確定已分割槽表的邊界值

1. 以下查詢對於  PartitionTable  表中的每個分割槽返回邊界值。

   
   

   SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue   
   FROM sys.tables AS t  
   JOIN sys.indexes AS i  
       ON t.object_id = i.object_id  
   JOIN sys.partitions AS p  
       ON i.object_id = p.object_id AND i.index_id = p.index_id   
   JOIN  sys.partition_schemes AS s   
       ON i.data_space_id = s.data_space_id  
   JOIN sys.partition_functions AS f   
       ON s.function_id = f.function_id  
   LEFT JOIN sys.partition_range_values AS r   
       ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
   WHERE t.name = 'PartitionTable' AND i.type <= 1  
   ORDER BY p.partition_number;
   

確定已分割槽表的分割槽列

1. 以下查詢返回表的分割槽列的名稱。   PartitionTable 中建立已分割槽表或索引。

   
   

   SELECT   
       t.[object_id] AS ObjectID   
       , t.name AS TableName   
       , ic.column_id AS PartitioningColumnID   
       , c.name AS PartitioningColumnName   
   FROM sys.tables AS t   
   JOIN sys.indexes AS i   
       ON t.[object_id] = i.[object_id]   
       AND i.[type] <= 1 -- clustered index or a heap   
   JOIN sys.partition_schemes AS ps   
       ON ps.data_space_id = i.data_space_id   
   JOIN sys.index_columns AS ic   
       ON ic.[object_id] = i.[object_id]   
       AND ic.index_id = i.index_id   
       AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column   
   JOIN sys.columns AS c   
       ON t.[object_id] = c.[object_id]   
       AND ic.column_id = c.column_id   
   WHERE t.name = 'PartitionTable' ;   
   GO
   

有關詳細資訊，請參閱：

• ALTER DATABASE 檔案和檔案組選項 (Transact-SQL)

• CREATE PARTITION FUNCTION (Transact-SQL)

• CREATE PARTITION SCHEME (Transact-SQL)

• CREATE TABLE (Transact-SQL)

2. 建立分割槽表步驟

　　建立分割槽表的步驟分為5步：

　　（1）建立資料庫檔案組

　　（2）建立資料庫檔案

　　（3）建立分割槽函式

　　（4）建立分割槽方案

　　（5）建立分割槽表

2.1> 建立資料庫檔案組

　　新建示例資料庫Northwind，建立資料庫檔案組和檔案，新增檔案組。

2.2> 建立資料庫檔案

　　建立資料檔案，併為資料檔案分配檔案組。

　　完成建立後的資料庫檔案資訊

　　透過SQL Server Profiler可以看到具體的建立資料庫的指令碼如下：

CREATE DATABASE [Northwind]
 CONTAINMENT = NONE ON  PRIMARY ( NAME = N'Northwind', FILENAME = N'F:\Database\Northwind\Northwind.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2010] ( NAME = N'Northwind_Data_2010', FILENAME = N'F:\Database\Northwind\Northwind_Data_2010.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2011] ( NAME = N'Northwind_Data_2011', FILENAME = N'F:\Database\Northwind\Northwind_Data_2011.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2012] ( NAME = N'Northwind_Data_2012', FILENAME = N'F:\Database\Northwind\Northwind_Data_2012.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2013] ( NAME = N'Northwind_Data_2013', FILENAME = N'F:\Database\Northwind\Northwind_Data_2013.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2014] ( NAME = N'Northwind_Data_2014', FILENAME = N'F:\Database\Northwind\Northwind_Data_2014.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Northwind_log', FILENAME = N'F:\Database\Northwind\Northwind_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)

　　檢視資料庫檔案組SQL語句：

2.3> 建立分割槽函式

　　建立分割槽函式Transact-SQL語法：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

　　引數：　　

　　partition_function_name：分割槽函式的名稱。 分割槽函式名稱在資料庫內必須唯一，並且符合識別符號的規則。

　　input_parameter_type：用於分割槽的列的資料型別。 當用作分割槽列時，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名資料型別或 CLR 使用者定義資料型別外，所有資料型別均有效。

　　boundary_value：為使用 partition_function_name 的已分割槽表或索引的每個分割槽指定邊界值。 如果 boundary_value 為空，則分割槽函式使 partition_function_name 將整個表或索引對映到單個分割槽。 只能使用 CREATE TABLE 或 CREATE INDEX 語句中指定的一個分割槽列。

　　LEFT | RIGHT 指定當間隔值由 資料庫引擎 按升序從左到右排序時，boundary_value [ ,...n ] 屬於每個邊界值間隔的哪一側（左側還是右側）。 如果未指定，則預設值為 LEFT。

　　示例：建立將用於Order表的分割槽函式

CREATE PARTITION FUNCTION Function_DateTime ( DATETIME )AS RANGE RIGHTFOR VALUES('2011-01-01', '2012-01-01', '2013-01-01', '2014-01-01')

　　完成建立分割槽函式之後，可以透過以下SQL語句檢視已建立的分割槽函式情況。

SELECT * FROM sys.partition_functions

2.4> 建立分割槽方案

　　分割槽方案的作用是將分割槽函式生成的分割槽對映到檔案組中去，分割槽方案是讓SQL Server將已分割槽的資料放在哪個檔案組中。

　　在當前資料庫中建立一個將已分割槽表或已分割槽索引的分割槽對映到檔案組的方案。 已分割槽表或已分割槽索引的分割槽的個數和域在分割槽函式中確定。 必須首先在 CREATE PARTITION FUNCTION 語句中建立分割槽函式，然後才能建立分割槽方案。

　　建立分割槽方案的Transact-SQL語法：

CREATE PARTITION SCHEME partition_scheme_nameAS PARTITION partition_function_name[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]

　　引數：

　　 partition_scheme_name ：分割槽方案的名稱。 分割槽方案名稱在資料庫中必須是唯一的，並且符合識別符號規則。

　　 partition_function_name ：使用分割槽方案的分割槽函式的名稱。 分割槽函式所建立的分割槽將對映到在分割槽方案中指定的檔案組。 partition_function_name 必須已經存在於資料庫中。 單個分割槽不能同時包含 FILESTREAM 和非 FILESTREAM 檔案組。

　　 ALL ：指定所有分割槽都對映到在 file_group_name 中提供的檔案組，或對映到主檔案組（如果指定了 [PRIMARY]。 如果指定了 ALL，則只能指定一個 file_group_name。

　　 file_group_name | [ PRIMARY ] [ ,...n] ：指定用來持有由 partition_function_name 指定的分割槽的檔案組的名稱。 file_group_name 必須已經存在於資料庫中。
　　如果指定了 [PRIMARY]，則分割槽將儲存於主檔案組中。 如果指定了 ALL，則只能指定一個 file_group_name。 分割槽分配到檔案組的順序是從分割槽 1 開始，按檔案組在 [,...n] 中列出的順序進行分配。 在 [,...n] 中，可以多次指定同一個 file_group_name。 如果 n 不足以擁有在 partition_function_name 中指定的分割槽數，則 CREATE PARTITION SCHEME 將失敗，並返回錯誤。
　　如果 partition_function_name 生成的分割槽數少於檔案組數，則第一個未分配的檔案組將標記為 NEXT USED，並且出現顯示命名 NEXT USED 檔案組的資訊。 如果指定了 ALL，則單獨的 file_group_name 將為該 partition_function_name 保持它的 NEXT USED 屬性。 如果在 ALTER PARTITION FUNCTION 語句中建立了一個分割槽，則 NEXT USED 檔案組將再接收一個分割槽。 若要再建立一個未分配的檔案組來擁有新的分割槽，請使用 ALTER PARTITION SCHEME。
　　在 file_group_name[ 1,...n] 中指定主檔案組時，必須像在 [PRIMARY] 中那樣分隔 PRIMARY，因為它是關鍵字。

　　示例：建立將用於Order表的分割槽方案

CREATE PARTITION SCHEME Scheme_DateTimeAS PARTITION Function_DateTimeTO ( SECTION2010, SECTION2011, SECTION2012, SECTION2013, SECTION2014 )

　　分割槽函式和分割槽方案建立之後，可以在資料庫的【儲存】中檢視：

　　透過可以透過以下SQL語句檢視已建立的分割槽方案：

SELECT * FROM sys.partition_schemes

2.5> 建立分割槽表

CREATE TABLE [Order](
    OrderID INT IDENTITY(1,1) NOT NULL,
    UserID INT NOT NULL,
    TotalAmount DECIMAL(18,2) NULL,
    OrderDate DATETIME NOT NULL) ON Scheme_DateTime ( OrderDate )

　　這裡需要注意分割槽表不能再建立聚集索引，因為聚集索引可以將記錄在物理上順序儲存，而分割槽表是將資料儲存在不同的表中，這兩個概念是衝突的，所以在建立分割槽表時不能再建立聚集索引。

　　完成Order表建立之後，檢視錶的屬性，可以看到Order表已經是分割槽表。

3. 操作分割槽表

3.1> Insert資料

USE [Northwind]GOINSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (1 ,10.00 ,'2009-10-20');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (1 ,20.50 ,'2009-12-31');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (2 ,40.00 ,'2010-01-20');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (3 ,40.00 ,'2010-10-20');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (4 ,50.00 ,'2011-10-20');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (5 ,60.00 ,'2012-10-20');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (5 ,60.00 ,'2012-10-20');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (6 ,70.00 ,'2013-10-20');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (10 ,90.00 ,'2014-10-20');INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (9 ,100.00 ,'2015-10-20');GO

3.2> 查詢資料所在物理分割槽表

　　在分割槽表中使用一般的SELECT語句無法知道資料是分別存放在哪幾個不同的物理表中，若要知道資料分別存放的物理表，可以使用$PARTITION函式，該函式可以呼叫分割槽函式並返回資料所在物理分割槽的編號。

　　$PARTITION的語法：$PARTITION.分割槽函式名(表示式)

SELECT $PARTITION.Function_DateTime('2010-01-01')

　　查詢結果分割槽函式返回為1，說明2010-01-01的資料會存放在第1個物理分割槽表中。

　　 使用$PARTITION函式可以具體知道每個物理分割槽表中存放了哪些記錄。

　　檢視物理分割槽表中存放的記錄：

SELECT * FROM [Order] WHERE $PARTITION.Function_DateTime(OrderDate) = 1

SELECT $PARTITION.Function_DateTime(OrderDate) AS 分割槽編號, COUNT(1) AS 記錄數 
FROM [Order]GROUP BY $PARTITION.Function_DateTime(OrderDate)

3.3> 修改分割槽表資料

UPDATE dbo.[Order] SET OrderDate='2015-01-01' WHERE OrderID = 3

4. 將普通錶轉換為分割槽表

　　一般的普通表都是在主鍵上建聚集索引，記錄的物理儲存位置由主鍵決定。

　　示例：建立一個Product普通表

CREATE TABLE Product
(
    ProductID INT IDENTITY(1,1) NOT NULL,
    ProductName VARCHAR(100) NOT NULL,
    UnitPrice DECIMAL(18,2) NULL,
    CreateDate DATETIME NOT NULL,    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID)
)

USE [Northwind]GOINSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to SQL' ,10 ,'2012-01-01');INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to XML' ,10 ,'2012-12-01');INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to Object' ,10 ,'2013-02-01');INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to ADO.NET' ,10 ,'2014-01-02');INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to Entity' ,10 ,'2015-01-01');GO

　　檢視錶Product的屬性：

　　檢視錶Product的索引，可以看到PK_Product為聚集索引。

　　將普通錶轉換為分割槽表的操作是先在普通表上刪除聚集索引，在建立一個新的聚集索引，在該聚集索引中使用分割槽方案。

　　在SQL Server中，主鍵欄位上預設建立聚集索引，刪除主鍵的聚集索引。

ALTER TABLE Product DROP CONSTRAINT PK_Product

　　重新建立主鍵非聚集索引

ALTER TABLE Product ADD CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductID ASC)

　　重新建立後的主鍵：

　　建立使用分割槽方案的聚集索引：

CREATE CLUSTERED INDEX IX_CreateDate ON Product ( CreateDate )ON Scheme_DateTime ( CreateDate )

　　調整後的Product表屬性：

　　調整後Product表記錄的物理儲存情況：

5. 刪除（合併）一個分割槽表

　　 刪除2012-01-01的分割槽，修改分割槽函式：

ALTER PARTITION FUNCTION Function_DateTime() MERGE RANGE ('2012-01-01')

　　在修改了分割槽函式之後，與之關聯的分割槽方案也將同時自動調整。在執行了上面合併分割槽的函式之後，檢視分割槽方案的Create指令碼。

CREATE PARTITION SCHEME [Scheme_DateTime] AS PARTITION [Function_DateTime] TO ([SECTION2010], [SECTION2011], [SECTION2013], [SECTION2014])

　　合併分割槽之後，被合併的分割槽記錄也將被重新分配物理儲存位置。

6. 新增分割槽

　　分割槽方案中指定的檔案組個數比分割槽函式中指定的邊界數大1，為分割槽方案指定一個可用的檔案組時，該分割槽方案並沒有立刻使用這個檔案組，只是將檔案組先備用著，等修改了分割槽函式之後分割槽方案才會使用這個檔案組。如果分割槽函式沒有更改，分割槽方案中的檔案組個數也不會更改。

 　　新增分割槽所需要使用到的檔案組可以使用之前合併分割槽之後沒有再使用的SECTION2012，也可以新建檔案組。

ALTER DATABASE [Northwind] ADD FILEGROUP [SECTION2015]

ALTER DATABASE [Northwind] ADD FILE ( 
    NAME = N'Northwind_Data_2015', 
    FILENAME = N'F:\Database\Northwind\Northwind_Data_2015.ndf' , 
    SIZE = 5120KB , 
    FILEGROWTH = 1024KB 
) TO FILEGROUP [SECTION2015]

　　為分割槽方案指定一個可用的檔案組：

ALTER PARTITION SCHEME Scheme_DateTime NEXT USED [SECTION2015]

　　修改分割槽函式，新增分割槽：

ALTER PARTITION FUNCTION Function_DateTime() SPLIT RANGE('2015-01-01')

　　檢視新增分割槽後的資料物理儲存：

跟著做，分割槽如此簡單

先跟著做一個分割槽表（分為11個分割槽），去除神秘的面紗，然後我們們再逐一擊破各個要點要害。

分割槽是要把一個表資料拆分為若干子集合，也就是把把一個資料檔案拆分到多個資料檔案中，然而這些檔案的存放可以依託一個檔案組或這多個檔案組，由於多個檔案組可以提高資料庫的訪問併發量，還可以把不同的分割槽配置到不同的磁碟中提高效率，所以建立時建議分割槽跟檔案組個數相同。

1.建立檔案組

可以點選資料庫屬性在檔案組裡面新增

T-sql語法：

alter database <資料庫名> add filegroup <檔案組名>

---建立資料庫檔案組alter database testSplit add filegroup ByIdGroup1alter database testSplit add filegroup ByIdGroup2alter database testSplit add filegroup ByIdGroup3alter database testSplit add filegroup ByIdGroup4alter database testSplit add filegroup ByIdGroup5alter database testSplit add filegroup ByIdGroup6alter database testSplit add filegroup ByIdGroup7alter database testSplit add filegroup ByIdGroup8alter database testSplit add filegroup ByIdGroup9alter database testSplit add filegroup ByIdGroup10

2.建立資料檔案到檔案組裡面

可以點選資料庫屬性在檔案裡面新增

T-sql語法：

alter database <資料庫名稱> add file <資料標識> to filegroup <檔案組名稱>--<資料標識> （name:檔名，fliename:物理路徑檔名，size:檔案初始大小kb/mb/gb/tb，filegrowth:檔案自動增量kb/mb/gb/tb/%,maxsize:檔案可以增加到的最大大小kb/mb/gb/tb/unlimited）

alter database testSplit add file (name=N'ById1',filename=N'J:\Work\資料庫\data\ById1.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup1alter database testSplit add file (name=N'ById2',filename=N'J:\Work\資料庫\data\ById2.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup2alter database testSplit add file (name=N'ById3',filename=N'J:\Work\資料庫\data\ById3.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup3alter database testSplit add file (name=N'ById4',filename=N'J:\Work\資料庫\data\ById4.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup4alter database testSplit add file (name=N'ById5',filename=N'J:\Work\資料庫\data\ById5.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup5alter database testSplit add file (name=N'ById6',filename=N'J:\Work\資料庫\data\ById6.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup6alter database testSplit add file (name=N'ById7',filename=N'J:\Work\資料庫\data\ById7.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup7alter database testSplit add file (name=N'ById8',filename=N'J:\Work\資料庫\data\ById8.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup8alter database testSplit add file (name=N'ById9',filename=N'J:\Work\資料庫\data\ById9.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup9alter database testSplit add file (name=N'ById10',filename=N'J:\Work\資料庫\data\ById10.ndf',size=5Mb,filegrowth=5mb)to filegroup ByIdGroup10

執行完成後，右鍵資料庫看檔案組跟檔案裡面是不是多出來啦這些檔案組跟檔案。

3.使用嚮導建立分割槽表

右鍵到要分割槽的表--- >> 儲存 --- >> 建立分割槽 --- >>顯示嚮導檢視 --- >> 下一步 --- >> 下一步。。

這裡舉例說下選擇列的意思：

假如你選擇的是int型別的列：那麼你的分割槽可以指定為1--100W是一個分割槽，100W--200W是一個分割槽....

假如你選擇的是datatime型別：那麼你的分割槽可以指定為：2014-01-01--2014-01-31一個分割槽，2014-02-01--2014-02-28一個分割槽...

根據這樣的列資料規則劃分，那麼在那個區間的資料，在插入資料庫時就被指向那個分割槽儲存下來。

我這裡選用orderid int型別 --- >> 下一步 --- >>

左邊界右邊界：就是把臨界值劃分給上一個分割槽還是下一個分割槽。一個小於號，一個小於等於號。

然後下一步下一步最後你會得到分割槽函式和分割槽方案。

USE [testSplit]GOBEGIN TRANSACTION--建立分割槽函式
CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000', N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')--建立分割槽方案CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3], [ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7], [ByIdGroup8], [ByIdGroup9], [ByIdGroup10])--建立分割槽索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] (    [OrderId])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])
--刪除分割槽索引
DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] WITH ( ONLINE = OFF )COMMIT TRANSACTION

執行上面嚮導生成的語句。分割槽完成。。

4.秀一下速度。

首先我在表中插入啦1千萬行資料。給表分啦11個分割槽。前十個分割槽裡面一個是100W條資料。。

說兩句：

可見反常現象，掃描次數跟邏輯讀取次數都是無分割槽表的2倍之多，但查詢速度卻是快啦不少啊。這就是分割槽的神奇之處啊，所以要相信這世界一切皆有可能。

分割槽函式，分割槽方案，分割槽表，分割槽索引

1.分割槽函式

指定分依據區列（依據列唯一），分割槽資料範圍規則，分割槽數量，然後將資料對映到一組分割槽上。

建立語法：  

create partition function 分割槽函式名(<分割槽列型別>) as range [left/right] for values (每個分割槽的邊界值,....)

--建立分割槽函式CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000', N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')

然而，分割槽函式只定義了分割槽的方法，此方法具體用在哪個表的那一列上，則需要在建立表或索引是指定。  

刪除語法：

--刪除分割槽語法drop partition function <分割槽函式名>

--刪除分割槽函式 bgPartitionFundrop partition function bgPartitionFun

需要注意的是，只有沒有應用到分割槽方案中的分割槽函式才能被刪除。

2.分割槽方案

指定分割槽對應的檔案組。

建立語法：  

--建立分割槽方案語法create partition scheme <分割槽方案名稱> as partition <分割槽函式名稱> [all]to (檔案組名稱,....)

--建立分割槽方案,所有分割槽在一個組裡面CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1])

分割槽函式必須關聯分割槽方案才能有效，然而分割槽方案指定的檔案組數量必須與分割槽數量一致，哪怕多個分割槽存放在一個檔案組中。

刪除語法：

--刪除分割槽方案語法drop partition scheme<分割槽方案名稱>

--刪除分割槽方案 bgPartitionSchemadrop partition scheme bgPartitionSchema1

只有沒有分割槽表，或索引使用該分割槽方案是，才能對其刪除。

3.分割槽表

建立語法：

--建立分割槽表語法create table <表名> (  <列定義>)on<分割槽方案名>(分割槽列名)

--建立分割槽表create table BigOrder (
   OrderId              int                  identity,
   orderNum             varchar(30)          not null,
   OrderStatus          int                  not null default 0,
   OrderPayStatus       int                  not null default 0,
   UserId               varchar(40)          not null,
   CreateDate           datetime             null default getdate(),
   Mark                 nvarchar(300)        null)on bgPartitionSchema(OrderId)

如果在表中建立主鍵或唯一索引，則分割槽依據列必須為該列。

4.分割槽索引

建立語法：  

--建立分割槽索引語法create <索引分類> index <索引名稱> on <表名>(列名)on <分割槽方案名>(分割槽依據列名)

--建立分割槽索引CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] (    [OrderId])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])

使用分割槽索引查詢，可以避免多個cpu操作多個磁碟時產生的衝突。

分割槽表明細資訊

這裡的語法，我就不寫啦，自己看語句分析吧。簡單的很。。

1.檢視分割槽依據列的指定值所在的分割槽  

--查詢分割槽依據列為10000014的資料在哪個分割槽上select $partition.bgPartitionFun(2000000)  --返回值是2，表示此值存在第2個分割槽

2.檢視分割槽表中，每個非空分割槽存在的行數

--檢視分割槽表中，每個非空分割槽存在的行數select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCountfrom bigordergroup by  $partition.bgPartitionFun(orderid)

3.檢視指定分割槽中的資料記錄  

---檢視指定分割槽中的資料記錄select * from bigorder where $partition.bgPartitionFun(orderid)=2

結果：資料從1000001開始到200W結束

分割槽的拆分與合併以及資料移動

 1.拆分分割槽

在分割槽函式中新增一個邊界值，即可將一個分割槽變為2個。

--分割槽拆分alter partition function bgPartitionFun()
split range(N'1500000')  --將第二個分割槽拆為2個分割槽

注意：如果分割槽函式已經指定了分割槽方案，則分割槽數需要和分割槽方案中指定的檔案組個數保持對應一致。

 2.合併分割槽

 與拆分分割槽相反，去除一個邊界值即可。

--合併分割槽alter partition function bgPartitionFun()
merge range(N'1500000')  --將第二第三分割槽合併

3.分割槽中的資料移動

 你或許會遇到這樣的需求，將普通表資料複製到分割槽表中，或者將分割槽表中的資料複製到普通表中。

 那麼移動資料這兩個表，則必須滿足下面的要求。

• 欄位數量相同，對應位置的欄位相同
• 相同位置的欄位要有相同的屬性，相同的型別。
• 兩個表在一個檔案組中

1.建立表時指定檔案組

--建立表create table <表名> (  <列定義>)on <檔案組名>

2.從分割槽表中複製資料到普通表

--將bigorder分割槽表中的第一分割槽資料複製到普通表中alter table bigorder switch partition 1 to <普通表名>

3.從普通標中複製資料到分割槽表中

這裡要注意的是要先將分割槽表中的索引刪除，即便普通表中存在跟分割槽表中相同的索引。

--將普通表中的資料複製到bigorder分割槽表中的第一分割槽alter table <普通表名> switch to bigorder partition 1

分割槽檢視

分割槽檢視是先建立帶有欄位約束的相同表,而約束不同,例如,第一個表的id約束為0--100W,第二表為101萬到200萬.....依次類推。

建立完一系列的表之後，用union all 連線起來建立一個檢視，這個檢視就形成啦分割槽視同。

很簡單的，這裡我主要是說分割槽表，就不說分割槽檢視啦。。

 檢視資料庫分割槽資訊

SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,      CASE pf.boundary_value_on_right            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS RowsFROM sys.partitions                  AS pJOIN sys.indexes                     AS i      ON i.object_id = p.object_id
      AND i.index_id = p.index_idJOIN sys.data_spaces                 AS ds      ON ds.data_space_id = i.data_space_idJOIN sys.partition_schemes           AS ps      ON ps.data_space_id = ds.data_space_idJOIN sys.partition_functions         AS pf      ON pf.function_id = ps.function_idJOIN sys.destination_data_spaces     AS dds2      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_numberJOIN sys.filegroups                  AS fg      ON fg.data_space_id = dds2.data_space_idLEFT JOIN sys.partition_range_values AS prv_left      ON ps.function_id = prv_left.function_id      AND prv_left.boundary_id = p.partition_number - 1LEFT JOIN sys.partition_range_values AS prv_right      ON ps.function_id = prv_right.function_id      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0UNION ALLSELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,      NULL                        AS UpperBoundaryValue,      NULL                        AS Boundary, 
      p.rows                      AS RowsFROM sys.partitions     AS pJOIN sys.indexes        AS i      ON i.object_id = p.object_id
      AND i.index_id = p.index_idJOIN sys.data_spaces    AS ds      ON ds.data_space_id = i.data_space_idJOIN sys.filegroups           AS fg      ON fg.data_space_id = i.data_space_idWHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber

SQL Server 2008將普通錶轉換成分割槽表

  (2017-07-26 10:35:48)

轉載 ▼

最近專案中，某個表資料量爆發時增長，單表已700w+，讀寫效能急劇下降，所以考慮加入分割槽表以解燃眉之急，後續還是要分表分庫，當然這是後話。下面簡要說一下將普通錶轉為分割槽表的步驟。

一、建立檔案組

開啟SQL Server Management Studio，在相關資料庫項右鍵屬性，進入資料庫屬性頁，選擇檔案組選項 ，新增所要的檔案組 

二、建立檔案

資料庫屬性頁，選擇檔案選項 ，新增檔案  。

新增檔案時，需要選擇上一步新增的對應檔案組

三、刪除普通表中的聚集索引，因為分割槽表 是以某個欄位為分割槽條件，所以，除了這個欄位以外不能再存在其他聚集索引的。要想將普通錶轉換成分割槽表，就必須要先刪除原表中聚集索引，然後再建立一個新的聚集索引，以此聚集索引中建立分割槽方案

1. --刪掉主鍵  
2. ALTER TABLE testTab DROP constraint PK_testTab  
3. --建立主鍵，但不設為聚集索引  
4. ALTER TABLE  testTab  ADD CONSTRAINT  PK_testTab   PRIMARY KEY NONCLUSTERED  
5. (  
6.     [ID] ASC  
7. ) ON [PRIMARY]  

四、建立一個分割槽函式

1. --建立一個分割槽函式
2. CREATE PARTITION FUNCTION part_month_func_range_test(datetime)  
3. AS RANGE RIGHT FOR VALUES (
4. '2017-7-1 00:00:00' ,
5. ' 2017-8-1 00:00:00 ' ,
6. ' 2017-8-1 00:00:00 ' ,
7. ' 2017-9-1 00:00:00 ',
8. ' 2017-10-1 00:00:00 ',
9. ' 2017-11-1 00:00:00 ',
10. )  

五、建立一個分割槽方案

1. CREATE PARTITION SCHEME partschSale  
2. AS PARTITION part_month_func_range_test
3. TO (  
4.   FC201706,  
5.   FC201707,  
6.   FC201708, 
7.   FC201709,  
8.   FC201710，
9.   FC201711，
10.   FC201712
11. )  

注意： 方案中檔案組比函式中要多一個

六、按分割槽方案建立聚集索引

1. --建立一個新的聚集索引，在該聚集索引中使用分割槽方案  
2. CREATE CLUSTERED INDEX CT_ testTab  ON Sale([inDate])  
3. ON  part_month_func_range_test ([ inDate ])  

為表建立了一個使用分割槽方案的聚集索引之後，該表就變成了一個分割槽表了。

sql server 分割槽表之檢視分割槽表的相關資料

在向分割槽表中插入資料方法和在普遍表中插入資料的方法是完全相同的，對於程式設計師而言，

不需要去理會這13條記錄研究放在哪個資料表中。當然，在查詢資料時，也可以不用理會數

據到底是存放在哪個物理上的資料表中。如使用以下SQL語句進行查詢：

select * from Sale  

 

    查詢的結果如下圖所示：

    從上面兩個步驟中，根本就感覺不到資料是分別存放在幾個不同的物理表中，因為在邏輯上，這

些資料都屬於同一個資料表。如果你非想知道哪條記錄是放在哪個物理上的分割槽表中，那麼就必須

使用到$PARTITION函式，這個函式的可以呼叫分割槽函式，並返回資料所在物理分割槽的編號。

    說起來有點難懂，不過用起來很簡單。$PARTITION的語法是：

    $PARTITION.分割槽函式名(表示式)

    假設，你想知道2010年10月1日的資料會放在哪個物理分割槽表中，你就可以使用以下語句來檢視。

select $PARTITION.partfunSale ('2010-10-1')  

 

    在以上語句中，partfunSale()為分割槽函式名，括號中的表示式必須是日期型的資料或可以隱式轉換成

日期型的資料，如果要問我為什麼， 那麼就回想一個怎麼定義分割槽函式的吧

（CREATE PARTITION FUNCTION partfunSale (datetime)）。

在定義partfunSale()函式時，指定了引數為日期型，所以括號中的表示式必須是日期型或可

以隱式轉換成日期型的資料。以上程式碼的執行結果如下圖所示：

    在該圖中可以看出，分割槽函式返回的結果為2，也就是說，2010年10月1日的資料會放在第2個物理分割槽表中。

 

    再進一步考慮，如果想具體知道每個物理分割槽表中存放了哪些記錄，也可以使用$PARTITION函式。

因為$PARTITION函式可以得到物理分割槽表的編號，那麼只要將$PARTITION.partfunSale(SaleTime)做

為where的條件使用即可，如以下程式碼 所示：

select * from Sale where $PARTITION.partfunSale(SaleTime)=1  

select * from Sale where $PARTITION.partfunSale(SaleTime)=2  

select * from Sale where $PARTITION.partfunSale(SaleTime)=3  

select * from Sale where $PARTITION.partfunSale(SaleTime)=4  

select * from Sale where $PARTITION.partfunSale(SaleTime)=5  

 

    以上程式碼的執行結果如下圖所示：

    從上圖中我們可以看到每個分割槽表中的資料記錄情況——和我們插入時設定的情況完全一致。同理可得

，如果要統計每個物理分割槽表中的記錄數，可以使用如下程式碼：

select $PARTITION.partfunSale(SaleTime) as 分割槽編號,count(id) as

 記錄數 from Sale group by $PARTITION.partfunSale(SaleTime)  

 

    以上程式碼的執行結果如下圖所示：

 

    除了在插入資料時程式設計師不需要去考慮分割槽表的物理情況之外，就是連修改資料也不需要考慮。

SQL Server會自動將記錄從一個分割槽表移到另一個分割槽表中，如以下程式碼所示：

--統計所有分割槽表中的記錄總數  

select $PARTITION.partfunSale(SaleTime) as 分割槽編號,count(id) as 記錄數 from

 Sale group by $PARTITION.partfunSale(SaleTime)  

--修改編號為1的記錄，將時間改為2019年1月1日  

update Sale set SaleTime='2019-1-1' where id=1  

--重新統計所有分割槽表中的記錄總數  

select $PARTITION.partfunSale(SaleTime) as 分割槽編號,count(id) as 記錄數 

from Sale group by $PARTITION.partfunSale(SaleTime)  

 

    在以上程式碼中，程式設計師將其中一條資料的時間改變了，從分割槽函式中可以得知，

這條記錄應該從第一個分割槽表移到第五個分割槽表中，如下圖所示。而整個操作過程，

程式設計師是完全不需要干預的。

About Me