當(dāng)前位置：首頁(yè) > 编程资源 > 编程问答 >内容正文

编程问答

分区sql2005

發(fā)布時(shí)間：2025/4/5 编程问答 16 豆豆

生活随笔收集整理的這篇文章主要介紹了分区sql2005 小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

摘要：SQL Server 2005 中基于表的分區(qū)功能為簡(jiǎn)化分區(qū)表的創(chuàng)建和維護(hù)過(guò)程提供了靈活性和更好的性能。追溯從邏輯分區(qū)表和手動(dòng)分區(qū)表的功能到最新分區(qū)功能的發(fā)展歷程，探索為什么、何時(shí)以及如何使用 SQL Server 2005 設(shè)計(jì)、實(shí)現(xiàn)和維護(hù)分區(qū)表。（本文包含一些指向英文站點(diǎn)的鏈接。）

關(guān)于本文本文所描繪的功能和計(jì)劃是下一版本 SQL Server 的開(kāi)發(fā)方向。它們并非本產(chǎn)品的說(shuō)明書(shū)，如有更改，恕不另行通知。對(duì)于最終產(chǎn)品是否具有這些功能不做任何明示或暗示的保證。對(duì)于某些功能，本文假設(shè)讀者熟悉 SQL Server 2000 功能和服務(wù)。有關(guān)背景信息，請(qǐng)?jiān)L問(wèn)SQL Server 網(wǎng)站或 SQL Server 2000 資源工具包。這并不是產(chǎn)品說(shuō)明書(shū)。

下載相關(guān)的代碼示例 SQL2005PartitioningScripts.exe。

為什么要進(jìn)行分區(qū)？

什么是分區(qū)？為什么要使用分區(qū)？簡(jiǎn)單的回答是：為了改善大型表以及具有各種訪問(wèn)模式的表的可伸縮性和可管理性。通常，創(chuàng)建表是為了存儲(chǔ)某種實(shí)體（例如客戶或銷(xiāo)售）的信息，并且每個(gè)表只具有描述該實(shí)體的屬性。一個(gè)表對(duì)應(yīng)一個(gè)實(shí)體是最容易設(shè)計(jì)和理解的，因此不需要優(yōu)化這種表的性能、可伸縮性和可管理性，尤其是在表變大的情況下。

大型表是由什么構(gòu)成的呢？超大型數(shù)據(jù)庫(kù) (VLDB) 的大小以數(shù)百 GB 計(jì)算，甚至以 TB 計(jì)算，但這個(gè)術(shù)語(yǔ)不一定能夠反映數(shù)據(jù)庫(kù)中各個(gè)表的大小。大型數(shù)據(jù)庫(kù)是指無(wú)法按照預(yù)期方式運(yùn)行的數(shù)據(jù)庫(kù)，或者運(yùn)行成本或維護(hù)成本超出預(yù)定維護(hù)要求或預(yù)算要求的數(shù)據(jù)庫(kù)。這些要求也適用于表；如果其他用戶的活動(dòng)或維護(hù)操作限制了數(shù)據(jù)的可用性，則可以認(rèn)為表非常大。例如，如果性能?chē)?yán)重下降，或者每天、每周甚至每個(gè)月的維護(hù)期間有兩個(gè)小時(shí)無(wú)法訪問(wèn)數(shù)據(jù)，則可以認(rèn)為銷(xiāo)售表非常大。有些情況下，周期性的停機(jī)時(shí)間是可以接受的，但是通過(guò)更好的設(shè)計(jì)和分區(qū)實(shí)現(xiàn)，通常可以避免或最大程度地減少這種情況的發(fā)生。雖然術(shù)語(yǔ) VLDB 僅適用于數(shù)據(jù)庫(kù)，但對(duì)分區(qū)來(lái)說(shuō)，了解表的大小更重要。

除了大小之外，當(dāng)表中的不同行集擁有不同的使用模式時(shí)，具有不同訪問(wèn)模式的表也可能會(huì)影響性能和可用性。盡管使用模式并不總是在變化（這也不是進(jìn)行分區(qū)的必要條件），但在使用模式發(fā)生變化時(shí)，通過(guò)分區(qū)可以進(jìn)一步改善管理、性能和可用性。還以銷(xiāo)售表為例，當(dāng)前月份的數(shù)據(jù)可能是可讀寫(xiě)的，但以往月份的數(shù)據(jù)（通常占表數(shù)據(jù)的大部分）是只讀的。在數(shù)據(jù)使用發(fā)生變化的類(lèi)似情況下，或在維護(hù)成本隨著在表中讀寫(xiě)數(shù)據(jù)的次數(shù)增加而變得異常龐大的情況下，表響應(yīng)用戶請(qǐng)求的能力可能會(huì)受到影響。相應(yīng)地，這也限制了服務(wù)器的可用性和可伸縮性。

此外，如果以不同的方式使用大量數(shù)據(jù)集，則需要經(jīng)常對(duì)靜態(tài)數(shù)據(jù)執(zhí)行維護(hù)操作。這可能會(huì)造成代價(jià)高昂的影響，例如性能問(wèn)題、阻塞問(wèn)題、備份（空間、時(shí)間和運(yùn)營(yíng)成本），還可能會(huì)對(duì)服務(wù)器的整體可伸縮性產(chǎn)生負(fù)面影響。

分區(qū)可以帶來(lái)什么幫助？當(dāng)表和索引變得非常大時(shí)，分區(qū)可以將數(shù)據(jù)分為更小、更容易管理的部分，從而提供一定的幫助。本文重點(diǎn)介紹橫向分區(qū)，在橫向分區(qū)中，大量的行組存儲(chǔ)在多個(gè)相互獨(dú)立的分區(qū)中。分區(qū)集的定義根據(jù)需要進(jìn)行自定義、定義和管理。Microsoft SQL Server 2005 允許您根據(jù)特定的數(shù)據(jù)使用模式，使用定義的范圍或列表對(duì)表進(jìn)行分區(qū)。SQL Server 2005 還圍繞新的表和索引結(jié)構(gòu)設(shè)計(jì)了幾種新功能，為分區(qū)表和索引的長(zhǎng)期管理提供了大量的選項(xiàng)。

此外，如果具有多個(gè) CPU 的系統(tǒng)中存在一個(gè)大型表，則對(duì)該表進(jìn)行分區(qū)可以通過(guò)并行操作獲得更好的性能。通過(guò)對(duì)各個(gè)并行子集執(zhí)行多項(xiàng)操作，可以改善在極大型數(shù)據(jù)集（例如數(shù)百萬(wàn)行）中執(zhí)行大規(guī)模操作的性能。通過(guò)分區(qū)改善性能的例子可以從以前版本中的聚集看出。例如，除了聚集成一個(gè)大型表外，SQL Server 還可以分別處理各個(gè)分區(qū)，然后將各個(gè)分區(qū)的聚集結(jié)果再聚集起來(lái)。在 SQL Server 2005 中，連接大型數(shù)據(jù)集的查詢可以通過(guò)分區(qū)直接受益；SQL Server 2000 支持對(duì)子集進(jìn)行并行連接操作，但需要?jiǎng)討B(tài)創(chuàng)建子集。在 SQL Server 2005 中，已分區(qū)為相同分區(qū)鍵和相同分區(qū)函數(shù)的相關(guān)表（如 Order 和 OrderDetails 表）被稱(chēng)為已對(duì)齊。當(dāng)優(yōu)化程序檢測(cè)到兩個(gè)已分區(qū)且已對(duì)齊的表連接在一起時(shí)，SQL Server 2005 可以先將同一分區(qū)中的數(shù)據(jù)連接起來(lái)，然后再將結(jié)果合并起來(lái)。這使 SQL Server 2005 可以更有效地使用具有多個(gè) CPU 的計(jì)算機(jī)。

分區(qū)的發(fā)展歷史

分區(qū)的概念對(duì) SQL Server 來(lái)說(shuō)并不陌生。實(shí)際上，此產(chǎn)品的每個(gè)版本中都可以實(shí)現(xiàn)不同形式的分區(qū)。但是，由于沒(méi)有為了幫助用戶創(chuàng)建和維護(hù)分區(qū)架構(gòu)而專(zhuān)門(mén)設(shè)計(jì)一些功能，因此分區(qū)一直是一個(gè)很繁瑣的過(guò)程，沒(méi)有得到充分的利用。而且，用戶和開(kāi)發(fā)人員對(duì)此架構(gòu)存在誤解（由于其數(shù)據(jù)庫(kù)設(shè)計(jì)比較復(fù)雜），低估了它的優(yōu)點(diǎn)。但是，由于概念中固有的重要性能改善，SQL Server 7.0 開(kāi)始通過(guò)分區(qū)視圖實(shí)現(xiàn)各種分區(qū)方式，以此來(lái)改進(jìn)這種功能。現(xiàn)在，SQL Server 2005 為通過(guò)分區(qū)表對(duì)大型數(shù)據(jù)集進(jìn)行分區(qū)又邁出了最大的一步。

對(duì) SQL Server 7.0 之前的版本中的對(duì)象進(jìn)行分區(qū)

在 SQL Server 6.5 及以前的版本中，分區(qū)只能通過(guò)設(shè)計(jì)來(lái)完成，還必須內(nèi)置到所有數(shù)據(jù)訪問(wèn)編碼和查詢方法中。通過(guò)創(chuàng)建多個(gè)表，然后通過(guò)存儲(chǔ)過(guò)程、視圖或客戶端應(yīng)用程序管理對(duì)正確表的訪問(wèn)，通常可以改善某些操作的性能，但代價(jià)是增加了設(shè)計(jì)的復(fù)雜性。每個(gè)用戶和開(kāi)發(fā)人員都必須知道（并正確引用）正確的表。單獨(dú)創(chuàng)建和管理每個(gè)分區(qū)，而使用視圖來(lái)簡(jiǎn)化訪問(wèn)；但是這種解決方案對(duì)性能并沒(méi)有太大的改善。使用聯(lián)合視圖簡(jiǎn)化用戶和應(yīng)用程序訪問(wèn)時(shí)，查詢處理器必須訪問(wèn)每個(gè)基礎(chǔ)表才能確定結(jié)果集所需的數(shù)據(jù)。如果只需要基礎(chǔ)表的有限子集，則每個(gè)用戶和開(kāi)發(fā)人員都必須了解此設(shè)計(jì)，以便只引用相應(yīng)的表。

SQL Server 7.0 中的分區(qū)視圖

在 SQL Server 7.0 之前的版本中，手動(dòng)創(chuàng)建分區(qū)所面臨的挑戰(zhàn)主要與性能有關(guān)。盡管視圖可以簡(jiǎn)化應(yīng)用程序設(shè)計(jì)、用戶訪問(wèn)和查詢的編寫(xiě)，但卻無(wú)法改善性能。而在 SQL Server 7.0 版本中，視圖結(jié)合了約束，允許查詢優(yōu)化程序從查詢計(jì)劃中刪除不相關(guān)的表（即分區(qū)消除），大大降低了聯(lián)合視圖訪問(wèn)多個(gè)表時(shí)的總計(jì)劃成本。

請(qǐng)參見(jiàn)圖 1 中的 YearlySales 視圖。您可以定義十二個(gè)單獨(dú)的表（如 SalesJanuary2003、SalesFebruary2003 等），然后定義每個(gè)季度的視圖以及全年的視圖 YearlySales，而不是將所有銷(xiāo)售數(shù)據(jù)放到一個(gè)大型表中。

圖 1：SQL Server 7.0/2000 中的分區(qū)視圖

使用以下查詢?cè)L問(wèn) YearlySales 視圖的用戶只會(huì)被引導(dǎo)至 SalesJanuary2003 表。

SELECT ys.* FROM dbo.YearlySales AS ys WHERE ys.SalesDate = '20030113'

只要約束可信并且訪問(wèn)視圖的查詢使用 WHERE 子句根據(jù)分區(qū)鍵（定義約束的列）限制查詢結(jié)果，SQL Server 就會(huì)只訪問(wèn)必需的基礎(chǔ)表。受信任的約束是指 SQL Server 能夠確保所有數(shù)據(jù)符合該約束所定義的屬性的約束。創(chuàng)建約束時(shí)，默認(rèn)行為是創(chuàng)建約束 WITH CHECK。此設(shè)置將導(dǎo)致對(duì)表執(zhí)行架構(gòu)鎖定，以便根據(jù)約束驗(yàn)證數(shù)據(jù)。如果驗(yàn)證結(jié)果表明現(xiàn)有數(shù)據(jù)有效，則添加約束；一旦解除架構(gòu)鎖定，后續(xù)的插入、更新和刪除操作都必須符合正在應(yīng)用的約束。通過(guò)使用此過(guò)程創(chuàng)建受信任的約束，開(kāi)發(fā)人員無(wú)需直接訪問(wèn)（甚至不需要知道）他們感興趣的表，從而大大降低了使用視圖的設(shè)計(jì)的復(fù)雜性。通過(guò)受信任的約束，SQL Server 可以從執(zhí)行計(jì)劃中刪除不需要的表，從而改善性能。

注意：約束可以通過(guò)各種方式變得“不可信任”；例如，如果未指定 CHECK_CONSTRAINTS 參數(shù)即執(zhí)行批量插入，或者使用 NOCHECK 創(chuàng)建約束。如果約束不可信任，查詢處理器將轉(zhuǎn)而掃描所有基礎(chǔ)表，因?yàn)樗鼰o(wú)法確定所請(qǐng)求的數(shù)據(jù)是否真的位于正確的基礎(chǔ)表中。

SQL Server 2000 中的分區(qū)視圖

盡管 SQL Server 7.0 大大簡(jiǎn)化了設(shè)計(jì)并改善了 SELECT 語(yǔ)句的性能，但是并沒(méi)有為數(shù)據(jù)修改語(yǔ)句帶來(lái)任何好處。INSERT、UPDATE 和 DELETE 語(yǔ)句只能針對(duì)基礎(chǔ)表，而不能直接針對(duì)用于聯(lián)合表的視圖。在 SQL Server 2000 中，數(shù)據(jù)修改語(yǔ)句還可以受益于 SQL Server 7.0 中引入的分區(qū)視圖功能。由于數(shù)據(jù)修改語(yǔ)句可以使用相同的分區(qū)視圖結(jié)構(gòu)，因此，SQL Server 可以通過(guò)視圖將修改定向至相應(yīng)的基礎(chǔ)表。為了正確配置此設(shè)置，需要對(duì)分區(qū)鍵及其創(chuàng)建設(shè)置額外的限制；但是，基本原理是相同的，因?yàn)?SELECT 查詢與修改都會(huì)直接發(fā)送給相應(yīng)的基礎(chǔ)表。有關(guān)在 SQL Server 2000 中進(jìn)行分區(qū)的限制、設(shè)置、配置和最佳方法的詳細(xì)信息，請(qǐng)參見(jiàn) Using Partitions in a Microsoft SQL Server 2000 Data Warehouse。

SQL Server 2005 中的分區(qū)表

盡管 SQL Server 7.0 和 SQL Server 2000 中的改進(jìn)大大改善了使用分區(qū)視圖時(shí)的性能，但是并沒(méi)有簡(jiǎn)化分區(qū)數(shù)據(jù)集的管理、設(shè)計(jì)或開(kāi)發(fā)。使用分區(qū)視圖時(shí)，必須單獨(dú)創(chuàng)建和管理每個(gè)基礎(chǔ)表（在其中定義視圖的表）。盡管簡(jiǎn)化了應(yīng)用程序設(shè)計(jì)并為用戶帶來(lái)了好處（用戶不再需要知道直接訪問(wèn)哪個(gè)基礎(chǔ)表），但是由于要管理的表太多，而且必須為每個(gè)表管理數(shù)據(jù)完整性約束，管理工作變得更復(fù)雜。因?yàn)楣芾矸矫娴膯?wèn)題，通常只有在需要存檔或加載數(shù)據(jù)時(shí)才使用分區(qū)視圖來(lái)分離表。當(dāng)數(shù)據(jù)被移動(dòng)到只讀表或從只讀表中刪除后，操作的代價(jià)變得十分高昂，不僅花費(fèi)時(shí)間、占據(jù)日志空間，通常還會(huì)導(dǎo)致系統(tǒng)阻塞。

另外，由于以前版本中的分區(qū)策略需要開(kāi)發(fā)人員創(chuàng)建各個(gè)表和索引，然后通過(guò)視圖將它們聯(lián)合起來(lái)，因此優(yōu)化程序需要驗(yàn)證并確定每個(gè)分區(qū)的計(jì)劃（因?yàn)樗饕赡芤寻l(fā)生變化）。這樣一來(lái)，SQL Server 2000 中的查詢優(yōu)化時(shí)間通常會(huì)隨著處理的分區(qū)數(shù)增加而直線上升。

在 SQL Server 2005 中，從定義上講，每個(gè)分區(qū)都擁有相同的索引。例如，請(qǐng)考慮這樣一種方案，即當(dāng)前月份的聯(lián)機(jī)事務(wù)處理 (OLTP) 數(shù)據(jù)需要移動(dòng)到每個(gè)月末的分析表中。分析表（用于只讀查詢）是具有一個(gè)群集索引和兩個(gè)非群集索引的表；批量加載 1 GB 數(shù)據(jù)（加載到已建立索引并激活的一個(gè)表中）將使當(dāng)前用戶遭受系統(tǒng)阻塞的情況，因?yàn)楸砗?或索引變得支離破碎和/或被鎖定。另外，因?yàn)槊總魅胍恍卸夹枰S護(hù)表和索引，所以加載過(guò)程還將耗費(fèi)大量的時(shí)間。雖然可以通過(guò)多種方法加快批量加載的速度，但這些方法可能會(huì)直接影響所有其他用戶，因?yàn)樽非笏俣榷鵁o(wú)法實(shí)現(xiàn)并發(fā)操作。

如果將這些數(shù)據(jù)單獨(dú)放到一個(gè)新創(chuàng)建的（空）且未建立索引（堆）的表中，則可以先加載數(shù)據(jù)，而在加載數(shù)據(jù)之后建立索引。通常情況下，使用這種架構(gòu)可以獲得十倍或更好的性能。實(shí)際上，通過(guò)加載未建立索引的表可以利用多個(gè) CPU，因?yàn)榭梢圆⑿屑虞d多個(gè)數(shù)據(jù)文件或從同一個(gè)文件中加載多個(gè)數(shù)據(jù)塊（通過(guò)開(kāi)始和結(jié)束行位置來(lái)定義）。由于兩個(gè)操作都可以通過(guò)并行獲益，因此可以更進(jìn)一步改善性能。

在 SQL Server 的任何版本中，分區(qū)都使您可以獲得更精確的控制，而且不需要將所有數(shù)據(jù)放到一個(gè)位置；但是，需要?jiǎng)?chuàng)建和管理許多對(duì)象。在以前的版本中，通過(guò)動(dòng)態(tài)創(chuàng)建表、刪除表以及修改聯(lián)合視圖，可以實(shí)現(xiàn)功能性分區(qū)策略。但是，SQL Server 2005 中的解決方案更加完善：您可以輕松地移入新填充的分區(qū)（作為現(xiàn)有分區(qū)架構(gòu)的額外分區(qū)），還可以移出任何舊分區(qū)。整個(gè)過(guò)程只需要很短的時(shí)間即可完成，通過(guò)使用并行批量加載和并行索引建立，還可以進(jìn)一步提高效率。更重要的是，因?yàn)榉謪^(qū)是在表范圍之外進(jìn)行管理的，所以添加分區(qū)之前不會(huì)對(duì)所查詢的表造成任何影響。結(jié)果是，添加一個(gè)分區(qū)通常只需要幾秒鐘。

需要?jiǎng)h除數(shù)據(jù)時(shí)的性能改善也很顯著。如果一個(gè)數(shù)據(jù)庫(kù)需要一個(gè)滑動(dòng)窗口數(shù)據(jù)集，用于移植新數(shù)據(jù)（例如當(dāng)前月份的數(shù)據(jù)）并刪除最早的數(shù)據(jù)（可能是上一年同一月份的數(shù)據(jù)），那么使用分區(qū)可以將數(shù)據(jù)移植的性能提高幾個(gè)數(shù)量級(jí)。雖然這看起來(lái)好像很大，但考慮了未分區(qū)的區(qū)別；當(dāng)所有數(shù)據(jù)位于一個(gè)表中時(shí)，刪除 1 GB 的舊數(shù)據(jù)需要對(duì)表及其相關(guān)索引進(jìn)行逐行處理。刪除數(shù)據(jù)的過(guò)程將創(chuàng)建大量的日志活動(dòng)，不允許在刪除的過(guò)程中出現(xiàn)日志截?cái)鄦?wèn)題（注意，刪除是一個(gè)自動(dòng)提交的事務(wù)；但是，可以通過(guò)盡可能地執(zhí)行多個(gè)刪除操作來(lái)控制事務(wù)的大小），因此，可能需要更大的日志。但是，如果使用分區(qū)，刪除相同數(shù)量的數(shù)據(jù)需要從分區(qū)表中刪除特定的分區(qū)（一種元數(shù)據(jù)操作），然后刪除或截?cái)嗒?dú)立的表。

此外，如果不知道如何才能最好地設(shè)計(jì)分區(qū)，則不可能認(rèn)識(shí)到將文件組與分區(qū)結(jié)合使用是實(shí)現(xiàn)分區(qū)的理想選擇。文件組允許您將各個(gè)表放置到不同的物理磁盤(pán)上。如果一個(gè)表包含多個(gè)文件（使用文件組），則無(wú)法預(yù)測(cè)數(shù)據(jù)的物理位置。對(duì)于不需要使用并行操作的系統(tǒng)來(lái)說(shuō)，SQL Server 可以在文件組之間更平均地使用所有磁盤(pán)，使數(shù)據(jù)具體放在什么位置變得不是那么重要，從而提高系統(tǒng)的性能。

注意：在圖 2 中，一個(gè)文件組包含三個(gè)文件。此文件組中放置了兩個(gè)表，即 Orders 和 OrderDetails。將表放置到文件組中時(shí)，SQL Server 將根據(jù)文件組中的對(duì)象需要的空間，從每個(gè)文件中獲得盤(pán)區(qū)分配（64-KB 塊，相當(dāng)于八個(gè) 8-KB 頁(yè)面），按比例填充文件組中的文件。創(chuàng)建 Orders 和 OrderDetails 表時(shí)，文件組是空的。創(chuàng)建訂單時(shí)，數(shù)據(jù)被輸入到 Orders 表中（每個(gè)訂單占據(jù)一行），并且按照每個(gè)明細(xì)項(xiàng)一行的方式輸入到 OrderDetails 表中。SQL Server 將一個(gè)盤(pán)區(qū)分配給文件 1 中的 Orders 表，將另一個(gè)盤(pán)區(qū)分配給文件 2 中的 OrderDetails 表。OrderDetails 表的增長(zhǎng)速度可能比 Orders 表快，后續(xù)的分配將轉(zhuǎn)到下一個(gè)需要空間的表中。隨著 OrderDetails 表的增長(zhǎng)，它將從文件 3 中獲取下一個(gè)盤(pán)區(qū)，而 SQL Server 將繼續(xù)在文件組的文件之間“循環(huán)”下去。在圖 2 中，就是從每個(gè)表到盤(pán)區(qū)，再?gòu)拿總€(gè)盤(pán)區(qū)到相應(yīng)的文件組。盤(pán)區(qū)是按照需要的空間進(jìn)行分配的，而根據(jù)流程進(jìn)行編號(hào)。

圖 2：使用文件組進(jìn)行分區(qū)填充

SQL Server 繼續(xù)在文件組中的所有對(duì)象之間平衡分配。如果增加給定操作使用的磁盤(pán)數(shù)，雖然 SQL Server 可以更有效地運(yùn)行，但從管理或維護(hù)的角度來(lái)說(shuō)，增加磁盤(pán)數(shù)并非最佳選擇，尤其是在使用模式幾乎可以預(yù)測(cè)（且已隔離）的情況下。因?yàn)閿?shù)據(jù)在磁盤(pán)上的位置并不明確，所以您無(wú)法隔離數(shù)據(jù)以執(zhí)行備份等維護(hù)操作。

通過(guò) SQL Server 2005 中的分區(qū)表，可以對(duì)表進(jìn)行設(shè)計(jì)（使用函數(shù)和架構(gòu)），從而將具有相同分區(qū)鍵的所有行都直接放置到（且總是轉(zhuǎn)到）特定的位置。函數(shù)用于定義分區(qū)邊界以及放置第一個(gè)值的分區(qū)。在使用 LEFT 分區(qū)函數(shù)時(shí)，第一個(gè)值將作為第一個(gè)分區(qū)中的上邊界。在使用 RIGHT 分區(qū)函數(shù)時(shí)，第一個(gè)值將作為第二個(gè)分區(qū)的下邊界（本文后面將更詳細(xì)地介紹分區(qū)函數(shù)）。定義函數(shù)后即可創(chuàng)建分區(qū)架構(gòu)，以定義分區(qū)到其數(shù)據(jù)庫(kù)位置的物理映射（根據(jù)分區(qū)函數(shù)）。當(dāng)多個(gè)表使用同一個(gè)函數(shù)（但不一定使用同一個(gè)架構(gòu)）時(shí)，將按類(lèi)似的方式對(duì)具有相同分區(qū)鍵的行進(jìn)行分組。此概念稱(chēng)為對(duì)齊。通過(guò)將來(lái)自多個(gè)表但具有相同分區(qū)鍵的行對(duì)齊到相同或不同的物理磁盤(pán)上，SQL Server 可以（如果優(yōu)化程序做出此選擇）只處理每個(gè)表中必要的數(shù)據(jù)組。要實(shí)現(xiàn)對(duì)齊，兩個(gè)分區(qū)表或索引所在的相應(yīng)分區(qū)之間必須具有某種對(duì)應(yīng)性。它們必須為分區(qū)列使用等效的分區(qū)函數(shù)。如果滿足以下條件，兩個(gè)分區(qū)函數(shù)則可以用來(lái)對(duì)齊數(shù)據(jù)：

?	兩個(gè)分區(qū)函數(shù)使用相同數(shù)量的參數(shù)和分區(qū)。
?	每個(gè)函數(shù)中使用的分區(qū)鍵具有相同的類(lèi)型（包括長(zhǎng)度和精度，如果適用，還包括縮放和排序）。
?	邊界值相等（包括 LEFT/RIGHT 邊界標(biāo)準(zhǔn)）。

注意：即使兩個(gè)分區(qū)函數(shù)都用于對(duì)齊數(shù)據(jù)，但如果沒(méi)有在與分區(qū)表相同的列上分區(qū)，最后的索引也可能無(wú)法對(duì)齊。

排序是一種更強(qiáng)大的對(duì)齊方式，通過(guò)排序，兩個(gè)對(duì)齊的對(duì)象將用一個(gè) equi-join 謂詞連接起來(lái)（equi-join 位于分區(qū)列上）。在可能出現(xiàn) equi-join 謂詞的查詢、子查詢或其他類(lèi)似結(jié)構(gòu)的上下文中，這變得很重要。排序之所以重要，因?yàn)樵诜謪^(qū)列上連接表的查詢一般都非常快。以圖 2 中的 Orders 和 OrderDetails 表為例，除了按比例填充文件之外，還可以創(chuàng)建映射到三個(gè)文件組的分區(qū)架構(gòu)。定義 Orders 和 OrderDetails 表時(shí)，將它們定義為使用相同的架構(gòu)。具有相同分區(qū)鍵值的相關(guān)數(shù)據(jù)將被放置到同一個(gè)文件中，而將必要的數(shù)據(jù)隔離出來(lái)以便進(jìn)行連接。如果來(lái)自多個(gè)表的相關(guān)行都按照相同的方式進(jìn)行分區(qū)，SQL Server 則可以連接分區(qū)，而無(wú)需在整個(gè)表或多個(gè)分區(qū)中（如果表使用了不同的分區(qū)函數(shù)）搜索匹配的行。在這種情況下，不僅可以對(duì)齊對(duì)象（因?yàn)樗鼈兪褂孟嗤逆I），還可以按存儲(chǔ)位置對(duì)齊（因?yàn)橄嗤臄?shù)據(jù)位于相同的文件中）。

圖 3 顯示兩個(gè)對(duì)象可以使用相同的分區(qū)架構(gòu)，而具有相同分區(qū)鍵的所有數(shù)據(jù)行最后將位于同一個(gè)文件組中。對(duì)齊相關(guān)數(shù)據(jù)后，SQL Server 2005 可以有效地并行處理大型數(shù)據(jù)集。例如，1 月份的所有銷(xiāo)售數(shù)據(jù)（包括 Orders 和 OrderDetails 表中的數(shù)據(jù)）都位于第一個(gè)文件組中，2 月份的數(shù)據(jù)位于第二個(gè)文件組中，依此類(lèi)推。

圖 3：按存儲(chǔ)位置對(duì)齊的表

SQL Server 允許根據(jù)范圍進(jìn)行分區(qū)，還允許將表和索引都設(shè)計(jì)為使用相同的架構(gòu)，以便更好地對(duì)齊。好的設(shè)計(jì)可以大大提高整體性能，但是，如果數(shù)據(jù)的使用隨著時(shí)間而發(fā)生變化，該怎么辦？如果需要額外的分區(qū)，又該怎么辦？簡(jiǎn)化從分區(qū)表外部添加分區(qū)、刪除分區(qū)和管理分區(qū)等方面的管理工作是 SQL Server 2005 的主要設(shè)計(jì)目標(biāo)。

SQL Server 2005 已經(jīng)考慮了如何簡(jiǎn)化分區(qū)的管理、開(kāi)發(fā)和使用。它在性能和可管理性方面有以下優(yōu)點(diǎn)：

?	簡(jiǎn)化了需要進(jìn)行分區(qū)以改善性能或可管理性的大型表的設(shè)計(jì)和實(shí)現(xiàn)。
?	將數(shù)據(jù)加載到現(xiàn)有分區(qū)表的新分區(qū)中時(shí)，最大程度地減少了對(duì)其他分區(qū)中的數(shù)據(jù)訪問(wèn)的影響。
?	將數(shù)據(jù)加載到現(xiàn)有分區(qū)表的新分區(qū)中時(shí)，性能相當(dāng)于將同樣的數(shù)據(jù)加載到新的空表中。
?	在存檔和/或刪除分區(qū)表的一個(gè)分區(qū)時(shí)，最大程度地減少了對(duì)表中其他分區(qū)的訪問(wèn)的影響。
?	允許通過(guò)將分區(qū)移入和移出分區(qū)表來(lái)維護(hù)分區(qū)。
?	提供了更好的伸縮性和并行性，可以對(duì)多個(gè)相關(guān)表執(zhí)行大量操作。
?	改善了所有分區(qū)的性能。
?	縮短了查詢優(yōu)化時(shí)間，因?yàn)椴恍枰獑为?dú)優(yōu)化每個(gè)分區(qū)。

定義和術(shù)語(yǔ)

要在 SQL Server 2005 中實(shí)現(xiàn)分區(qū)，必須了解一些新的概念、術(shù)語(yǔ)和語(yǔ)法。要理解這些新概念，首先我們看一下與創(chuàng)建和放置操作有關(guān)的表結(jié)構(gòu)。在以前的版本中，表通常是一個(gè)物理和邏輯概念，但使用 SQL Server 2005 分區(qū)表和索引，您在存儲(chǔ)表的方式和位置方面就有了多種選擇。在 SQL Server 2005 中，可以使用以前版本中的相同語(yǔ)法創(chuàng)建表和索引，作為一個(gè)表結(jié)構(gòu)放置到 DEFAULT 文件組或用戶定義的文件組中。另外，在 SQL Server 2005 中，還可以根據(jù)分區(qū)架構(gòu)創(chuàng)建表和索引。分區(qū)架構(gòu)可以將對(duì)象映射到一個(gè)或多個(gè)文件組。為了確定數(shù)據(jù)的相應(yīng)物理位置，分區(qū)架構(gòu)將使用了分區(qū)函數(shù)。分區(qū)函數(shù)定義了用來(lái)定向行的算法，而架構(gòu)則將分區(qū)與其相應(yīng)的物理位置（即文件組）相關(guān)聯(lián)。換句話說(shuō)，表仍然是一個(gè)邏輯概念，但與以前的版本相比，表在磁盤(pán)上的物理位置有了很大的不同；表還可以擁有架構(gòu)。

范圍分區(qū)

范圍分區(qū)是按照特定和可定制的數(shù)據(jù)范圍定義的表分區(qū)。范圍分區(qū)的邊界由開(kāi)發(fā)人員選擇，還可以隨著數(shù)據(jù)使用模式的變化而變化。通常，這些范圍是根據(jù)日期或排序后的數(shù)據(jù)組進(jìn)行劃分的。

范圍分區(qū)主要用于數(shù)據(jù)存檔、決策支持（當(dāng)通常只需要特定范圍內(nèi)的數(shù)據(jù)時(shí)，例如給定的月份或季度）以及組合的 OLTP 和決策支持系統(tǒng) (DSS)（數(shù)據(jù)使用在行的生命周期內(nèi)會(huì)發(fā)生變化）。SQL Server 2005 分區(qū)表和索引的最大優(yōu)點(diǎn)，尤其是在存檔和維護(hù)方面，就是可以管理特定范圍內(nèi)的數(shù)據(jù)。通過(guò)范圍分區(qū)，可以非常快速地存檔和替換舊的數(shù)據(jù)。當(dāng)數(shù)據(jù)訪問(wèn)通常用于對(duì)大范圍數(shù)據(jù)的決策支持時(shí)，最適合使用范圍分區(qū)。在這種情況下，數(shù)據(jù)所在的具體位置至關(guān)重要，這樣才能在需要時(shí)只訪問(wèn)相應(yīng)的分區(qū)。另外，由于事務(wù)數(shù)據(jù)已經(jīng)可用，因此可以輕松快捷地添加數(shù)據(jù)。范圍分區(qū)最初定義起來(lái)很復(fù)雜，因?yàn)樾枰獮槊總€(gè)分區(qū)定義邊界條件。此外，還需要?jiǎng)?chuàng)建一個(gè)架構(gòu)，將每個(gè)分區(qū)映射到一個(gè)或多個(gè)文件組。但是，它們通常具有一致的模式，因此，定義后很容易通過(guò)編程方式進(jìn)行維護(hù)（參見(jiàn)圖 4）。

圖 4：具有 12 個(gè)分區(qū)的范圍分區(qū)表

定義分區(qū)鍵

對(duì)表和索引進(jìn)行分區(qū)的第一步就是定義分區(qū)的關(guān)鍵數(shù)據(jù)。分區(qū)鍵必須作為一個(gè)列存在于表中，還必須滿足一定的條件。分區(qū)函數(shù)定義鍵（也稱(chēng)為數(shù)據(jù)的邏輯分離）所基于的數(shù)據(jù)類(lèi)型。函數(shù)只定義鍵，而不定義數(shù)據(jù)在磁盤(pán)上的物理位置。數(shù)據(jù)的位置由分區(qū)架構(gòu)決定。換句話說(shuō)，架構(gòu)將數(shù)據(jù)映射到一個(gè)或多個(gè)文件組，文件組將數(shù)據(jù)映射到特定的文件，文件又將數(shù)據(jù)映射到磁盤(pán)。分區(qū)架構(gòu)通常使用函數(shù)來(lái)實(shí)現(xiàn)此目的：如果函數(shù)定義了五個(gè)分區(qū)，則架構(gòu)必須使用五個(gè)文件組。文件組不需要各不相同；但是，如果擁有多個(gè)磁盤(pán)（最好是多個(gè) CPU），使用不同的文件組可以獲得更好的性能。將架構(gòu)與表一起使用時(shí)，您需要定義用作分區(qū)函數(shù)的參數(shù)的列。

對(duì)于范圍分區(qū)，數(shù)據(jù)集可以根據(jù)邏輯和數(shù)據(jù)驅(qū)動(dòng)的邊界進(jìn)行劃分。實(shí)際上，數(shù)據(jù)分區(qū)不可能實(shí)現(xiàn)真正的平衡。當(dāng)以定義分析的特定邊界（也稱(chēng)為范圍）的方式使用表時(shí)，數(shù)據(jù)的使用即表明范圍分區(qū)。范圍函數(shù)的分區(qū)鍵可以只包含一個(gè)列，而分區(qū)函數(shù)可以包含整個(gè)域，即使表中可能不存在數(shù)據(jù)（由于數(shù)據(jù)完整性/約束）。換句話說(shuō)，可以為每個(gè)分區(qū)定義邊界，但第一個(gè)分區(qū)和最后一個(gè)分區(qū)可能包含最左側(cè)的行（小于最低邊界條件的值）和最右側(cè)的行（大于最高邊界條件的值）。因此，要將值域限制到特定的數(shù)據(jù)集，必須將分區(qū)與 CHECK 約束結(jié)合使用。使用 CHECK 約束強(qiáng)制應(yīng)用業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性約束，使您可以將數(shù)據(jù)集限制到特定的范圍，而不是不確定的范圍。當(dāng)維護(hù)和管理過(guò)程中需要定期存檔大量數(shù)據(jù)，當(dāng)查詢?cè)L問(wèn)范圍子集內(nèi)的大量數(shù)據(jù)時(shí)，范圍分區(qū)是理想的選擇。

索引分區(qū)

除了對(duì)表的數(shù)據(jù)集進(jìn)行分區(qū)之外，還可以對(duì)索引進(jìn)行分區(qū)。使用相同的函數(shù)對(duì)表及其索引進(jìn)行分區(qū)通常可以優(yōu)化性能。當(dāng)索引和表按照相同的順序使用相同的分區(qū)函數(shù)和列時(shí)，表和索引將對(duì)齊。如果在已經(jīng)分區(qū)的表中建立索引，SQL Server 會(huì)自動(dòng)將新索引與該表的分區(qū)架構(gòu)對(duì)齊，除非該索引的分區(qū)明顯不同。當(dāng)表及其索引對(duì)齊后，SQL Server 則可以更有效地將分區(qū)移入和移出分區(qū)表，因?yàn)樗邢嚓P(guān)的數(shù)據(jù)和索引都使用相同的算法進(jìn)行劃分。

如果定義表和索引時(shí)不僅使用了相同的分區(qū)函數(shù)，還使用了相同的分區(qū)架構(gòu)，則這些表和索引將被認(rèn)為是按存儲(chǔ)位置對(duì)齊。按存儲(chǔ)位置對(duì)齊的一個(gè)優(yōu)點(diǎn)是，相同邊界內(nèi)的所有數(shù)據(jù)都位于相同的物理磁盤(pán)上。在這種情況下，可以單獨(dú)在某個(gè)時(shí)間段內(nèi)執(zhí)行備份操作，還可以根據(jù)數(shù)據(jù)的變化在備份頻率和備份類(lèi)型方面改變您的策略。如果連接或收集了相同文件或文件組中的表和索引，則可以發(fā)現(xiàn)更多的好處。SQL Server 可以通過(guò)在多個(gè)分區(qū)中并行操作來(lái)獲益。在按存儲(chǔ)位置對(duì)齊和多 CPU 的情況下，每個(gè)處理器都可以直接處理特定的文件或文件組，而不會(huì)與數(shù)據(jù)訪問(wèn)產(chǎn)生任何沖突，因?yàn)樗行枰臄?shù)據(jù)都位于同一個(gè)磁盤(pán)上。這樣，可以并行運(yùn)行多個(gè)進(jìn)程，而不會(huì)相互干擾。

有關(guān)詳細(xì)信息，請(qǐng)參見(jiàn) SQL Server Books Online 中的“Special Guidelines for Partitioned Indexes”。

分區(qū)的特殊情況：拆分、合并和移動(dòng)

為了更好地使用分區(qū)表，需要了解與分區(qū)管理有關(guān)的幾個(gè)新功能和概念。因?yàn)榉謪^(qū)適用于可以縮放的大型表，所以創(chuàng)建分區(qū)函數(shù)時(shí)選擇的分區(qū)數(shù)隨著時(shí)間而變化。可以將 ALTER TABLE 語(yǔ)句與新的拆分選項(xiàng)結(jié)合使用，在表中添加一個(gè)分區(qū)。拆分分區(qū)時(shí)，可以將數(shù)據(jù)移動(dòng)到新的分區(qū)中；但是為了維護(hù)性能，不應(yīng)移動(dòng)行。本文后面的案例研究將介紹這種方案。

相反，要?jiǎng)h除分區(qū)，請(qǐng)先移出數(shù)據(jù)，然后合并邊界點(diǎn)。如果使用范圍分區(qū)，則通過(guò)指明應(yīng)刪除的邊界點(diǎn)來(lái)發(fā)出合并請(qǐng)求。在只需要特定時(shí)段的數(shù)據(jù)并且定期進(jìn)行數(shù)據(jù)存檔（例如，每月一次）的情況下，您可能希望在當(dāng)前月份的數(shù)據(jù)可用時(shí)存檔部分?jǐn)?shù)據(jù)（最早月份的數(shù)據(jù)）。例如，您可以選擇獲取一年的數(shù)據(jù)，而在每個(gè)月末移入當(dāng)前月份，然后移出最早的月份，從而區(qū)分當(dāng)前月份的讀/寫(xiě) OLTP 與以前月份的只讀數(shù)據(jù)。如以下方案所述，可以通過(guò)一個(gè)特殊的操作流使處理更有效。

您保留了一年的只讀數(shù)據(jù)。目前，表中的數(shù)據(jù)是從 2003 年 9 月到 2004 年 8 月的數(shù)據(jù)。而當(dāng)前月份 2004 年 9 月位于另一個(gè)數(shù)據(jù)庫(kù)中，并為 OLTP 性能而進(jìn)行了優(yōu)化。在只讀版本的表中，共有 13 個(gè)分區(qū)：十二個(gè)分區(qū)包含數(shù)據(jù)（從 2003 年 9 月到 2004 年 8 月），最后一個(gè)分區(qū)是空的。最后這個(gè)分區(qū)為空的原因在于，范圍分區(qū)總是包括整個(gè)域，即最左側(cè)和最右側(cè)。如果您打算在滑動(dòng)窗口方案中管理數(shù)據(jù)，通常需要有一個(gè)可以拆分的空分區(qū)，以便放置新數(shù)據(jù)。在使用 LEFT 邊界點(diǎn)定義的分區(qū)函數(shù)中，空分區(qū)邏輯上位于最右側(cè)。將最后一個(gè)分區(qū)保留為空使您可以拆分空分區(qū)（用來(lái)存儲(chǔ)即將產(chǎn)生的新數(shù)據(jù)），而且不需要將最后一個(gè)分區(qū)的行（因?yàn)椴淮嬖?#xff09;移動(dòng)到添加（拆分空分區(qū)以存儲(chǔ)其他數(shù)據(jù)塊時(shí)進(jìn)行此操作）的新文件組中。這是一個(gè)相當(dāng)復(fù)雜的概念，將在本文后面的案例研究中進(jìn)行更詳細(xì)的介紹，但其核心思想是，所有數(shù)據(jù)添加或刪除操作都應(yīng)該只是元數(shù)據(jù)操作。為了確保只進(jìn)行元數(shù)據(jù)操作，需要從策略上管理表中不斷變化的那個(gè)分區(qū)。為了確保此分區(qū)為空的，您需要使用 CHECK 約束將此數(shù)據(jù)限定在基礎(chǔ)表中。在這種情況下，OrderDate 應(yīng)該在 2003 年 9 月 1 日之后（包括此日），在 2004 年 9 月 1 日之前。如果最后定義的邊界點(diǎn)是 8 月 31 日 11:59:59.997（為什么是 997，后文有詳細(xì)的說(shuō)明），則分區(qū)函數(shù)與此約束的組合將使最后一個(gè)分區(qū)為空。雖然這些只是概念，但重要的是要了解拆分和合并是通過(guò) ALTER PARTITION FUNCTION 進(jìn)行的，而移動(dòng)是通過(guò) ALTER TABLE 進(jìn)行的。

圖 5：加載/存檔數(shù)據(jù)前的范圍分區(qū)邊界

進(jìn)入 10 月份后（在 OLTP 數(shù)據(jù)庫(kù)中），9 月份的數(shù)據(jù)應(yīng)移到分區(qū)表中，用于進(jìn)行分析。移入和移出表的過(guò)程非常快，而且準(zhǔn)備工作可以在分區(qū)表外完成。后面的案例研究中會(huì)對(duì)此方案進(jìn)行深入的解釋，但核心思想是，您可以使用“分段表”，該表最終將成為分區(qū)表中的一個(gè)分區(qū)。本文后面的案例研究中會(huì)詳細(xì)介紹此方案。在此過(guò)程中，您將表的一個(gè)分區(qū)移出（參見(jiàn)圖 6）到相同文件組內(nèi)的一個(gè)非分區(qū)表中。因?yàn)橄嗤募M中已經(jīng)存在非分區(qū)表（這是成功的關(guān)鍵），SQL Server 可以將此移動(dòng)視為元數(shù)據(jù)更改。因?yàn)橹皇窃獢?shù)據(jù)更改，所以可以在幾秒鐘內(nèi)完成，而不需要執(zhí)行可能需要幾小時(shí)并在大型表中產(chǎn)生阻塞的刪除操作。移出此分區(qū)后，您仍然擁有 13 個(gè)分區(qū)；第一個(gè)（最舊的）分區(qū)現(xiàn)在是空的，最后一個(gè)（最近的，也是空的）分區(qū)是需要拆分的。

圖 6：移出分區(qū)

要?jiǎng)h除最舊的分區(qū)（2003 年 9 月），請(qǐng)將新的合并選項(xiàng)（如圖 7 所示）與 ALTER TABLE 結(jié)合使用。有效地合并邊界點(diǎn)將刪除邊界點(diǎn)，從而刪除分區(qū)。這將加載數(shù)據(jù)的分區(qū)數(shù)減少到 n-1（本例中為 12）。如果不需要移動(dòng)行，合并分區(qū)應(yīng)該是一個(gè)非常快的操作（因?yàn)橐喜⒌倪吔琰c(diǎn)沒(méi)有數(shù)據(jù)行）。在本例中，因?yàn)榈谝粋€(gè)分區(qū)為空，不需要從第一個(gè)分區(qū)向第二個(gè)分區(qū)中移動(dòng)任何行。如果在第一個(gè)分區(qū)非空的情況下合并邊界點(diǎn)，必須將第一個(gè)分區(qū)的行移動(dòng)到第二個(gè)分區(qū)中，這可能是一個(gè)代價(jià)非常高昂的操作。但是，在最常見(jiàn)的滑動(dòng)窗口方案中（空分區(qū)與活動(dòng)分區(qū)合并，并且不移動(dòng)任何行），不需要執(zhí)行此操作。

圖 7：合并分區(qū)

最后，必須將新表移入分區(qū)表。要將此操作作為元數(shù)據(jù)更改來(lái)執(zhí)行，必須在新表中（分區(qū)表的邊界之外）加載和建立索引。要移入分區(qū)，請(qǐng)先將最后一個(gè)范圍和最近一個(gè)空范圍拆分為兩個(gè)分區(qū)。另外，還需要更新表的約束以允許新的范圍。分區(qū)表將再次擁有 13 個(gè)分區(qū)。在滑動(dòng)窗口方案中，使用 LEFT 分區(qū)函數(shù)的最后一個(gè)分區(qū)將始終為空。

圖 8：拆分分區(qū)

最后，新加載的數(shù)據(jù)已準(zhǔn)備就緒，可以移入第十二個(gè)分區(qū)，即 2004 年 9 月。

圖 9：移入分區(qū)

表的結(jié)果是：

圖 10：加載/存檔數(shù)據(jù)后的范圍分區(qū)邊界

因?yàn)橐淮沃荒芴砑踊騽h除一個(gè)分區(qū)，所以應(yīng)重新創(chuàng)建需要添加或刪除多個(gè)分區(qū)的表。要更改為這種新的分區(qū)結(jié)構(gòu)，請(qǐng)先創(chuàng)建新的分區(qū)表，然后將數(shù)據(jù)加載到新創(chuàng)建的表中。與每次拆分后重新平衡整個(gè)表相比，這種方法更好。此過(guò)程是使用新的分區(qū)函數(shù)和新的分區(qū)架構(gòu)，然后將數(shù)據(jù)移動(dòng)到新分區(qū)的表中來(lái)完成的。要移動(dòng)數(shù)據(jù)，請(qǐng)先使用 INSERT newtable SELECT columnlist FROM oldtable 復(fù)制數(shù)據(jù)，然后刪除原始表。用戶不應(yīng)在此過(guò)程中修改數(shù)據(jù)，以防數(shù)據(jù)丟失。

有關(guān)詳細(xì)信息，請(qǐng)參見(jiàn) SQL Server Books Online 中的“ALTER PARTITION FUNCTION”和“ALTER TABLE”。

創(chuàng)建分區(qū)表的步驟

現(xiàn)在，您對(duì)分區(qū)表的價(jià)值有了一定的了解，下一節(jié)將詳細(xì)介紹實(shí)現(xiàn)分區(qū)表的過(guò)程以及有助于完成此過(guò)程的功能。邏輯流程如下：

圖 11：創(chuàng)建分區(qū)表或索引的步驟

確定是否應(yīng)為對(duì)象分區(qū)

雖然分區(qū)可以帶來(lái)眾多的好處，但也增加了實(shí)現(xiàn)對(duì)象的管理費(fèi)用和復(fù)雜性，這可能是得不償失的。尤其是，您可能不需要為較小的表或目前滿足性能和維護(hù)要求的表分區(qū)。前面提到的銷(xiāo)售方案使用分區(qū)減輕了移動(dòng)行和數(shù)據(jù)的負(fù)擔(dān)，但在決定是否實(shí)現(xiàn)分區(qū)時(shí)，您應(yīng)考慮您的方案是否存在這種負(fù)擔(dān)。

確定分區(qū)鍵和分區(qū)數(shù)

如果您正在嘗試改善大型數(shù)據(jù)子集的性能和可管理性，并且已經(jīng)定義了訪問(wèn)模式，則可以使用范圍分區(qū)減少數(shù)據(jù)爭(zhēng)用的情況，同時(shí)減少只讀數(shù)據(jù)不需要分區(qū)時(shí)的維護(hù)工作。要確定分區(qū)數(shù)，應(yīng)先評(píng)估您的數(shù)據(jù)中是否存在邏輯分組和模式。如果您通常一次只處理這些已定義子集中的少數(shù)幾個(gè)，則應(yīng)定義范圍以隔離查詢，使其只處理相應(yīng)的數(shù)據(jù)（即，只處理特定的分區(qū)）。

有關(guān)詳細(xì)信息，請(qǐng)參見(jiàn)SQL Server Books Online中的“Designing Partitioned Tables and Indexes”。

確定是否應(yīng)使用多個(gè)文件組

為了有助于優(yōu)化性能和維護(hù)，應(yīng)使用文件組分離數(shù)據(jù)。文件組的數(shù)目一定程度上由硬件資源決定：一般情況下，文件組數(shù)最好與分區(qū)數(shù)相同，并且這些文件組通常位于不同的磁盤(pán)上。但是，這主要適用于打算對(duì)整個(gè)數(shù)據(jù)集進(jìn)行分析的系統(tǒng)。如果您有多個(gè) CPU，SQL Server 則可以并行處理多個(gè)分區(qū)，從而大大縮短處理大量復(fù)雜報(bào)表和分析的總體時(shí)間。這種情況下，可以獲得并行處理以及在分區(qū)表中移入和移出分區(qū)的好處。

創(chuàng)建文件組

如果需要為多個(gè)文件放置一個(gè)分區(qū)表以獲得更好的 I/O 平衡，則至少需要?jiǎng)?chuàng)建一個(gè)文件組。文件組可以由一個(gè)或多個(gè)文件構(gòu)成，而每個(gè)分區(qū)必須映射到一個(gè)文件組。一個(gè)文件組可以由多個(gè)分區(qū)使用，但是為了更好地管理數(shù)據(jù)（例如，為了獲得更精確的備份控制），應(yīng)該對(duì)分區(qū)表進(jìn)行設(shè)計(jì)，以便只有相關(guān)數(shù)據(jù)或邏輯分組的數(shù)據(jù)位于同一個(gè)文件組中。使用 ALTER DATABASE，可以添加邏輯文件組名，然后添加文件。要為 AdventureWorks 數(shù)據(jù)庫(kù)創(chuàng)建名為 2003Q3 的文件組，請(qǐng)按以下方式使用 ALTER DATABASE：

ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]

創(chuàng)建文件組后，使用 ALTER DATABASE 將文件添加到該文件組中。

ALTER DATABASE AdventureWorks ADD FILE (NAME = N'2003Q3', FILENAME = N'C:\AdventureWorks\2003Q3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [2003Q3]

通過(guò)在 CREATE TABLE 的 ON 子句中指定一個(gè)文件組，可以為文件創(chuàng)建一個(gè)表。但是，如果表未分區(qū)，則不能為多個(gè)文件組創(chuàng)建一個(gè)表。要為一個(gè)文件組創(chuàng)建表，請(qǐng)使用 CREATE TABLE 的 ON 子句。要?jiǎng)?chuàng)建分區(qū)表，必須先確定分區(qū)的功能機(jī)制。進(jìn)行分區(qū)的標(biāo)準(zhǔn)以分區(qū)函數(shù)的形式從邏輯上與表相分離。此分區(qū)函數(shù)作為獨(dú)立于表的定義存在，而這種物理分離將起到幫助作用，因?yàn)槎鄠€(gè)對(duì)象都可以使用該分區(qū)函數(shù)。因此，為表分區(qū)的第一步是創(chuàng)建分區(qū)函數(shù)。

為范圍分區(qū)創(chuàng)建分區(qū)函數(shù)

范圍分區(qū)必須使用邊界條件進(jìn)行定義。而且，即使通過(guò) CHECK 約束對(duì)表進(jìn)行了限制，也不能消除該范圍任一邊界的值。為了允許定期將數(shù)據(jù)移入該表，需要?jiǎng)?chuàng)建最后一個(gè)空分區(qū)。

在范圍分區(qū)中，首先定義邊界點(diǎn)：如果存在五個(gè)分區(qū)，則定義四個(gè)邊界點(diǎn)值，并指定每個(gè)值是第一個(gè)分區(qū)的上邊界 (LEFT) 還是第二個(gè)分區(qū)的下邊界 (RIGHT)。根據(jù) LEFT 或 RIGHT 指定，始終有一個(gè)空分區(qū)，因?yàn)樵摲謪^(qū)沒(méi)有明確定義的邊界點(diǎn)。

具體來(lái)講，如果分區(qū)函數(shù)的第一個(gè)值（或邊界條件）是 '20001001'，則邊界分區(qū)中的值將是：

對(duì)于 LEFT

第一個(gè)分區(qū)是所有小于或等于 '20001001' 的數(shù)據(jù)

第二個(gè)分區(qū)是所有大于 '20001001' 的數(shù)據(jù)

對(duì)于 RIGHT

第一個(gè)分區(qū)是所有小于 '20001001' 的數(shù)據(jù)

第二個(gè)分區(qū)是所有大于或等于 '20001001' 數(shù)據(jù)

由于范圍分區(qū)可能在 datetime 數(shù)據(jù)中進(jìn)行定義，因此必須了解其含義。使用 datetime 具有某種含義：即總是同時(shí)指定日期和時(shí)間。未定義時(shí)間值的日期表示時(shí)間部分為“0”的 12:00 A.M。如果將 LEFT 與此類(lèi)數(shù)據(jù)結(jié)合使用，則日期為 10 月 1 日 12:00 A.M. 的數(shù)據(jù)將位于第一個(gè)分區(qū)，而 10 月份的其他數(shù)據(jù)將位于第二個(gè)分區(qū)。從邏輯上講，最好將開(kāi)始值與 RIGHT 結(jié)合使用，而將結(jié)束值與 LEFT 結(jié)合使用。下面的三個(gè)子句將創(chuàng)建邏輯上相同的分區(qū)結(jié)構(gòu)：

RANGE LEFT FOR VALUES ('20000930 23:59:59.997', '20001231 23:59:59.997', '20010331 23:59:59.997', '20010630 23:59:59.997')

或

RANGE RIGHT FOR VALUES ('20001001 00:00:00.000', '20010101 00:00:00.000', '20010401 00:00:00.000', '20010701 00:00:00.000')

或

RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')

注意：此處使用 datetime 數(shù)據(jù)類(lèi)型確實(shí)增加了一定的復(fù)雜性，但您需要確保設(shè)置正確的邊界情況。請(qǐng)注意使用 RIGHT 的簡(jiǎn)單性，因?yàn)槟J(rèn)時(shí)間為 12:00:00.000 A.M。對(duì)于 LEFT，復(fù)雜性增加是因?yàn)?datetime 數(shù)據(jù)類(lèi)型具有精度。必須選擇 23:59:59.997 的原因在于，datetime 數(shù)據(jù)無(wú)法保證毫秒級(jí)別的精度。相反，datetime 數(shù)據(jù)的精度在 3.33 毫秒內(nèi)。使用 23:59:59.999 這個(gè)確切的時(shí)間值是不行的，因?yàn)樵撝祵⒈簧崛氲阶罱咏臅r(shí)間值，即第二天的 12:00:00.000 A.M。由于進(jìn)行了這種舍入，將無(wú)法正確定義邊界。對(duì)于 datetime 數(shù)據(jù)，必須對(duì)明確提供的毫秒值加倍小心。

注意：分區(qū)函數(shù)還允許將函數(shù)作為分區(qū)函數(shù)定義的一部分。您可以使用 DATEADD(ms,-3,'20010101')，而不是使用 '20001231 23:59:59.997' 明確定義時(shí)間。

有關(guān)詳細(xì)信息，請(qǐng)參見(jiàn)SQL Server Books Online的“Transact-SQL Reference”中的“Date and Time”部分。

要在四個(gè)活動(dòng)分區(qū)（每個(gè)分區(qū)代表一個(gè)日歷季度）中存儲(chǔ)四分之一的 Orders 數(shù)據(jù)，并創(chuàng)建第五個(gè)分區(qū)以備將來(lái)使用（還是作為占位符，用于在分區(qū)表中移入和移出數(shù)據(jù)），請(qǐng)將 LEFT 分區(qū)函數(shù)與以下四個(gè)邊界條件結(jié)合使用：

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime) AS RANGE LEFT FOR VALUES ('20000930 23:59:59.997', '20001231 23:59:59.997', '20010331 23:59:59.997', '20010630 23:59:59.997')

記住，定義四個(gè)邊界點(diǎn)將創(chuàng)建五個(gè)分區(qū)。通過(guò)查看以下數(shù)據(jù)集檢查此分區(qū)創(chuàng)建的數(shù)據(jù)集：

邊界點(diǎn) '20000930 23:59:59.997' 作為 LEFT（設(shè)置模式）：

最左側(cè)的分區(qū)將包含所有小于或等于 '20000930 23:59:59.997' 的值

邊界點(diǎn) '20001231 23:59:59.997'：

第二個(gè)分區(qū)將包含所有大于 '20000930 23:59:59.997' 但小于或等于 '20001231 23:59:59.997' 的值

邊界點(diǎn) '20010331 23:59:59.997'：

第三個(gè)分區(qū)將包含所有大于 '20001231 23:59:59.997' 但小于或等于 '20010331 23:59:59.997' 的值

邊界點(diǎn) '20010630 23:59:59.997'：

第四個(gè)分區(qū)將包含所有大于 '20010331 23:59:59.997' 但小于或等于 '20010630 23:59:59.997' 的值

最后，第五個(gè)分區(qū)將包含所有大于 '20010630 23:59:59.997' 的值。

創(chuàng)建分區(qū)架構(gòu)

創(chuàng)建分區(qū)函數(shù)后，必須將其與分區(qū)架構(gòu)相關(guān)聯(lián)，以便將分區(qū)定向至特定的文件組。定義分區(qū)架構(gòu)時(shí)，即使多個(gè)分區(qū)位于同一個(gè)文件組中，也必須為每個(gè)分區(qū)指定一個(gè)文件組。對(duì)于前面創(chuàng)建的范圍分區(qū) (OrderDateRangePFN)，存在五個(gè)分區(qū)；最后一個(gè)空分區(qū)將在 PRIMARY 文件組中創(chuàng)建。因?yàn)榇朔謪^(qū)永遠(yuǎn)不包含數(shù)據(jù)，所以不需要指定特殊的位置。

CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION OrderDateRangePFN TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])

注意：如果所有分區(qū)都位于同一個(gè)文件組中，則可以使用以下更簡(jiǎn)單的語(yǔ)法：

CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION OrderDateRangePFN ALL TO ([PRIMARY])

創(chuàng)建分區(qū)表

定義分區(qū)函數(shù)（邏輯結(jié)構(gòu)）和分區(qū)架構(gòu)（物理結(jié)構(gòu)）后，即可創(chuàng)建表來(lái)利用它們。表定義應(yīng)使用的架構(gòu)，而架構(gòu)又定義函數(shù)。要將這三者結(jié)合起來(lái)，必須指定應(yīng)該應(yīng)用分區(qū)函數(shù)的列。范圍分區(qū)始終只映射到表中的一列，此列應(yīng)與分區(qū)函數(shù)中定義的邊界條件的數(shù)據(jù)類(lèi)型相匹配。另外，如果表應(yīng)明確限制數(shù)據(jù)集（而不是從負(fù)無(wú)窮大到正無(wú)窮大），則還應(yīng)添加 CHECK 約束。

CREATE TABLE [dbo].[OrdersRange] ( [PurchaseOrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL , [RevisionNumber] [tinyint] NULL , [ModifiedDate] [datetime] NULL , [ShipMethodID] [tinyint] NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NOT NULL CONSTRAINT OrdersRangeYear CHECK ([OrderDate] >= '20030701' AND [OrderDate] <= '20040630 11:59:59.997'), [TotalDue] [money] NULL ) ON OrderDatePScheme (OrderDate) GO

建立索引：是否分區(qū)？

默認(rèn)情況下，分區(qū)表中創(chuàng)建的索引也使用相同的分區(qū)架構(gòu)和分區(qū)列。如果屬于這種情況，索引將與表對(duì)齊。盡管未作要求，但將表與其索引對(duì)齊可以使管理工作更容易進(jìn)行，對(duì)于滑動(dòng)窗口方案尤其如此。

例如，要?jiǎng)?chuàng)建唯一的索引，分區(qū)列必須是一個(gè)關(guān)鍵列；這將確保對(duì)相應(yīng)的分區(qū)進(jìn)行驗(yàn)證，以保證索引的唯一性。因此，如果需要在一列上對(duì)表進(jìn)行分區(qū)，而必須在另一個(gè)列上創(chuàng)建唯一的索引，這些表和索引將無(wú)法對(duì)齊。在這種情況下，可以在唯一的列（如果是多列的唯一鍵，則可以是任一關(guān)鍵列）中對(duì)索引進(jìn)行分區(qū)，或者根本就不進(jìn)行分區(qū)。請(qǐng)注意，在分區(qū)表中移入和移出數(shù)據(jù)時(shí)，必須刪除和創(chuàng)建此索引。

注意：如果您打算使用現(xiàn)有數(shù)據(jù)加載表并立即在其中添加索引，則通常可以通過(guò)以下方式獲得更好的性能：先加載到未分區(qū)、未建立索引的表中，然后在加載數(shù)據(jù)后創(chuàng)建分區(qū)索引。通過(guò)為分區(qū)架構(gòu)定義群集索引，可以在加載數(shù)據(jù)后更有效地為表分區(qū)。這也是為現(xiàn)有表分區(qū)的不錯(cuò)方法。要?jiǎng)?chuàng)建與未分區(qū)表相同的表并創(chuàng)建與已分區(qū)群集索引相同的群集索引，請(qǐng)用一個(gè)文件組目標(biāo)位置替換創(chuàng)建表中的 ON 子句。然后，在加載數(shù)據(jù)之后為分區(qū)架構(gòu)創(chuàng)建群集索引。

融會(huì)貫通：案例研究

如果您閱讀了與分區(qū)有關(guān)的概念、優(yōu)點(diǎn)和代碼示例，則可能已對(duì)此過(guò)程有了一個(gè)很好的理解；但是，對(duì)于每個(gè)步驟，都可以使用特定的設(shè)置和選項(xiàng)，而且在某些情況下，還必須滿足各種條件。本節(jié)將幫助您將這些內(nèi)容融會(huì)貫通起來(lái)進(jìn)行理解。

范圍分區(qū)：銷(xiāo)售數(shù)據(jù)

銷(xiāo)售數(shù)據(jù)的使用方式經(jīng)常發(fā)生變化。當(dāng)前月份的數(shù)據(jù)是事務(wù)數(shù)據(jù)，而上一個(gè)月份的數(shù)據(jù)主要用于進(jìn)行分析。分析通常針對(duì)月份、季度和/或年度范圍的數(shù)據(jù)進(jìn)行。因?yàn)椴煌姆治鋈藛T可能希望同時(shí)查看大量不斷變化的數(shù)據(jù)，所以通過(guò)分區(qū)可以更好地隔離此活動(dòng)。在此方案中，活動(dòng)數(shù)據(jù)來(lái)自 283 個(gè)分支位置，而且是通過(guò)兩個(gè)標(biāo)準(zhǔn)格式的 ASCII 文件傳輸?shù)摹Ｔ诿總€(gè)月第一天的上午 3 點(diǎn)之前，所有文件均被放置到一臺(tái)中央文件服務(wù)器上。所有文件按大小進(jìn)行排列，但每月平均約有 86,000 份銷(xiāo)售（訂單）。每個(gè)訂單平均包含 2.63 個(gè)明細(xì)項(xiàng)，因此，OrderDetails 文件平均包含 226,180 行。每月增加約 2,500 萬(wàn)個(gè)新的 Orders 和 6,400 萬(wàn)個(gè) OrderDetails 行，而歷史分析服務(wù)器要使兩年的數(shù)據(jù)都處于活動(dòng)狀態(tài)以便進(jìn)行分析。兩年的數(shù)據(jù)剛好低于 6 億個(gè) Orders 和超過(guò) 15 億個(gè) OrderDetails 行。因?yàn)榉治鐾ǔＪ窃谕患径鹊牟煌路葜g進(jìn)行比較，或與上一年度的相同月份進(jìn)行比較，所以可以使用范圍分區(qū)。每個(gè)范圍的邊界都是按月份確定的。

按照?qǐng)D 11 描述的步驟，使用基于 OrderDate 的范圍分區(qū)對(duì)表進(jìn)行分區(qū)。了解這臺(tái)新服務(wù)器的要求后，分析人員打算收集和分析連續(xù)六個(gè)月的數(shù)據(jù)，或當(dāng)前年度與上一年度三個(gè)月份（例如 2003 年 1 月到 3 月與 2004 年 1 月到 3 月）的數(shù)據(jù)。要使磁盤(pán)分區(qū)最大化并隔離大多數(shù)數(shù)據(jù)組，多個(gè)文件組將使用相同的物理磁盤(pán)，但是這些文件組將相差六個(gè)月以減少磁盤(pán)爭(zhēng)用。當(dāng)前數(shù)據(jù)是 2004 年 10 月，而所有 283 個(gè)存儲(chǔ)位置都在本地管理其當(dāng)前銷(xiāo)售。服務(wù)器上存儲(chǔ)了從 2002 年 10 月到 2004 年 9 月的數(shù)據(jù)。為了利用新的 16 向多處理器計(jì)算機(jī)和存儲(chǔ)區(qū)域網(wǎng)絡(luò)，每個(gè)月的文件存儲(chǔ)在一個(gè)文件組中，同時(shí)位于一個(gè)分區(qū)鏡像 (RAID 1+0) 磁盤(pán)集上。對(duì)于數(shù)據(jù)通過(guò)文件組在邏輯驅(qū)動(dòng)器上的物理布局，下圖（圖 12）描述了每月數(shù)據(jù)的位置。

圖 12：分區(qū)表的順序

12 個(gè)邏輯驅(qū)動(dòng)器都位于 RAID 1+0 配置中，因此 Orders 和 OrderDetails 數(shù)據(jù)所需的總磁盤(pán)數(shù)為 48 個(gè)。存儲(chǔ)區(qū)域網(wǎng)絡(luò)支持 78 個(gè)磁盤(pán)，而另外 30 個(gè)用于事務(wù)日志、TempDB、系統(tǒng)數(shù)據(jù)庫(kù)和其他更小的表，例如 Customers（900 萬(wàn)）和 Products（386,750 行）。Orders 和 OrderDetails 表都使用相同的邊界條件、磁盤(pán)位置和分區(qū)架構(gòu)。結(jié)果是（只看圖 13 中的兩個(gè)邏輯驅(qū)動(dòng)器 [驅(qū)動(dòng)器 E:\ 和 F:\]），相同月份的 Orders 和 OrderDetails 的數(shù)據(jù)都存儲(chǔ)在相同的磁盤(pán)上：

點(diǎn)擊查看大圖

圖 13：磁盤(pán)陣列上盤(pán)區(qū)位置的范圍分區(qū)

雖然看起來(lái)很復(fù)雜，但創(chuàng)建過(guò)程非常簡(jiǎn)單。設(shè)計(jì)分區(qū)表最難的部分在于從大量數(shù)據(jù)源傳輸數(shù)據(jù)，即 283 個(gè)存儲(chǔ)位置都必須使用一種標(biāo)準(zhǔn)的傳輸機(jī)制。但是，中央服務(wù)器上只定義了一個(gè) Orders 表和一個(gè) OrderDetails 表。要將兩個(gè)表都創(chuàng)建為分區(qū)表，請(qǐng)先創(chuàng)建分區(qū)函數(shù)和分區(qū)架構(gòu)。分區(qū)架構(gòu)定義分區(qū)在磁盤(pán)上的物理位置，因此必須存在文件組。在此表中，文件組是必需的，因此下一步是創(chuàng)建文件組。每個(gè)文件組的語(yǔ)法都與下面的語(yǔ)法相同，但必須創(chuàng)建所有 24 個(gè)文件組。有關(guān)創(chuàng)建所有 24 個(gè)文件組的完整腳本，請(qǐng)參見(jiàn) RangeCaseStudyFilegroups.sql 腳本。

注意：如果沒(méi)有指定相應(yīng)的驅(qū)動(dòng)器號(hào)，將無(wú)法運(yùn)行此腳本；但是此腳本包含一個(gè)“setup”表，可以修改此表以簡(jiǎn)化測(cè)試。您可以將驅(qū)動(dòng)器號(hào)/位置更改為一個(gè)驅(qū)動(dòng)器，以測(cè)試和學(xué)習(xí)語(yǔ)法。同時(shí)，確保將文件大小調(diào)整為 MB 而不是 GB，并根據(jù)可用的磁盤(pán)空間考慮指定一個(gè)較小的初始大小。

將為 SalesDB 數(shù)據(jù)庫(kù)創(chuàng)建 24 個(gè)文件和文件組。每個(gè)文件和文件組都具有相同的語(yǔ)法，只是位置、文件名和文件組名不相同：

ALTER DATABASE SalesDB ADD FILE (NAME = N'SalesDBFG1File1', FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf', SIZE = 20GB, MAXSIZE = 35GB, FILEGROWTH = 5GB) TO FILEGROUP [FG1] GO

創(chuàng)建所有 24 個(gè)文件和文件組后，即可定義分區(qū)函數(shù)和分區(qū)架構(gòu)。要驗(yàn)證文件和文件組，請(qǐng)分別使用 sp_helpfile 和 sp_helpfilegroup。

分區(qū)函數(shù)將在 OrderDate 列中進(jìn)行定義。使用的數(shù)據(jù)類(lèi)型為 datetime，而且兩個(gè)表都需要存儲(chǔ) OrderDate 才能根據(jù)此值對(duì)兩個(gè)表進(jìn)行分區(qū)。實(shí)際上，如果根據(jù)相同的鍵值對(duì)兩個(gè)表進(jìn)行分區(qū)，則分區(qū)鍵值屬于重復(fù)信息，但它對(duì)于獲得對(duì)齊優(yōu)點(diǎn)又是必需的。而且，在大多數(shù)情況下，應(yīng)該是一個(gè)相當(dāng)窄的列（datetime 數(shù)據(jù)類(lèi)型為 8 個(gè)字節(jié)）。如本文前面的“為范圍分區(qū)創(chuàng)建分區(qū)函數(shù)”部分所述，此函數(shù)將是一個(gè)范圍分區(qū)函數(shù)，其中的第一個(gè)邊界條件位于 LEFT（第一個(gè)）分區(qū)中。

CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime) AS RANGE LEFT FOR VALUES ('20021031 23:59:59.997', -- 2002 年 10 月 '20021130 23:59:59.997', -- 2002 年 11 月 '20021231 23:59:59.997', -- 2002 年 12 月 '20030131 23:59:59.997', -- 2003 年 1 月 '20030228 23:59:59.997', -- 2003 年 2 月 '20030331 23:59:59.997', -- 2003 年 3 月 '20030430 23:59:59.997', -- 2003 年 4 月 '20030531 23:59:59.997', -- 2003 年 5 月 '20030630 23:59:59.997', -- 2003 年 6 月 '20030731 23:59:59.997', -- 2003 年 7 月 '20030831 23:59:59.997', -- 2003 年 8 月 '20030930 23:59:59.997', -- 2003 年 9 月 '20031031 23:59:59.997', -- 2003 年 10 月 '20031130 23:59:59.997', -- 2003 年 11 月 '20031231 23:59:59.997', -- 2003 年 12 月 '20040131 23:59:59.997', -- 2004 年 1 月 '20040229 23:59:59.997', -- 2004 年 2 月 '20040331 23:59:59.997', -- 2004 年 3 月 '20040430 23:59:59.997', -- 2004 年 4 月 '20040531 23:59:59.997', -- 2004 年 5 月 '20040630 23:59:59.997', -- 2004 年 6 月 '20040731 23:59:59.997', -- 2004 年 7 月 '20040831 23:59:59.997', -- 2004 年 8 月 '20040930 23:59:59.997') -- 2004 年 9 月 GO

因?yàn)榘俗钭髠?cè)和最右側(cè)的邊界情況，所以此分區(qū)函數(shù)將創(chuàng)建 25 個(gè)分區(qū)。該表將保留第 25 個(gè)分區(qū)為空白。不需要為這個(gè)空分區(qū)指定特殊的文件組（因?yàn)槠渲杏肋h(yuǎn)不會(huì)包含數(shù)據(jù)）作為限制表數(shù)據(jù)的約束。要將數(shù)據(jù)定向至相應(yīng)的磁盤(pán)，可以使用分區(qū)架構(gòu)將分區(qū)映射到文件組。分區(qū)架構(gòu)將為 24 個(gè)將要包含數(shù)據(jù)的文件組使用明確的文件組名，而為第 25 個(gè)空分區(qū)使用 PRIMARY 文件組。

CREATE PARTITION SCHEME [TwoYearDateRangePScheme] AS PARTITION TwoYearDateRangePFN TO ( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10],[FG11],[FG12], [FG13],[FG14],[FG15],[FG16],[FG17],[FG18], [FG19],[FG20],[FG21],[FG22],[FG23],[FG24], [PRIMARY] ) GO

通過(guò)使用默認(rèn)的文件組或用戶定義的文件組作為未分區(qū)的表，或者使用架構(gòu)創(chuàng)建分區(qū)表，可以使用與以前的版本支持的相同語(yǔ)法創(chuàng)建表。哪種方法更好取決于表的填充方式和創(chuàng)建的分區(qū)數(shù)。從性能角度看，先填充堆再建立群集索引可能要?jiǎng)龠^(guò)在已經(jīng)建立索引的表中加載數(shù)據(jù)。另外，如果有多個(gè) CPU，您可以通過(guò)并行 BULK INSERT 語(yǔ)句將數(shù)據(jù)加載到表中，然后也以并行方式建立索引。對(duì)于 Orders 表，按照正常的方式創(chuàng)建表，然后通過(guò) INSERT SELECT 語(yǔ)句（從 AdventureWorks 示例數(shù)據(jù)庫(kù)中提取數(shù)據(jù)）加載現(xiàn)有的數(shù)據(jù)。要將 Orders 表建為分區(qū)表，請(qǐng)?jiān)谠摫淼?ON 子句中指定分區(qū)架構(gòu)。Orders 表是使用以下語(yǔ)法創(chuàng)建的：

CREATE TABLE SalesDB.[dbo].[Orders] ( [PurchaseOrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [ModifiedDate] [datetime] NULL, [ShipMethodID] tinyint NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NULL CONSTRAINT OrdersRangeYear CHECK ([OrderDate] >= '20021001' AND [OrderDate] < '20041001'), [TotalDue] [money] NULL ) ON TwoYearDateRangePScheme(OrderDate) GO

因?yàn)?OrderDetails 表也將使用此架構(gòu)，而且必須包含 OrderDate，所以使用以下語(yǔ)法創(chuàng)建 OrderDetails 表：

CREATE TABLE [dbo].[OrderDetails]( [OrderID] [int] NOT NULL, [LineNumber] [smallint] NOT NULL, [ProductID] [int] NULL, [UnitPrice] [money] NULL, [OrderQty] [smallint] NULL, [ReceivedQty] [float] NULL, [RejectedQty] [float] NULL, [OrderDate] [datetime] NOT NULL CONSTRAINT OrderDetailsRangeYearCK CHECK ([OrderDate] >= '20021001' AND [OrderDate] < '20041001'), [DueDate] [datetime] NULL, [ModifiedDate] [datetime] NOT NULL CONSTRAINT [OrderDetailsModifiedDateDFLT] DEFAULT (getdate()), [LineTotal] AS (([UnitPrice]*[OrderQty])), [StockedQty] AS (([ReceivedQty]-[RejectedQty])) ) ON TwoYearDateRangePScheme(OrderDate) GO

加載數(shù)據(jù)的下一步是通過(guò)兩個(gè) INSERT 語(yǔ)句處理的。這兩個(gè)語(yǔ)句使用新的 AdventureWorks 數(shù)據(jù)庫(kù)（從中復(fù)制數(shù)據(jù)）。請(qǐng)安裝 AdventureWorks 示例數(shù)據(jù)庫(kù)以復(fù)制此數(shù)據(jù)：

INSERT dbo.[Orders] SELECT o.[PurchaseOrderID] , o.[EmployeeID] , o.[VendorID] , o.[TaxAmt] , o.[Freight] , o.[SubTotal] , o.[Status] , o.[RevisionNumber] , o.[ModifiedDate] , o.[ShipMethodID] , o.[ShipDate] , o.[OrderDate] , o.[TotalDue] FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o WHERE ([OrderDate] >= '20021001' AND [OrderDate] < '20041001') GO INSERT dbo.[OrderDetails] SELECT od.PurchaseOrderID , od.LineNumber , od.ProductID , od.UnitPrice , od.OrderQty , od.ReceivedQty , od.RejectedQty , o.OrderDate , od.DueDate , od.ModifiedDate FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o ON o.PurchaseOrderID = od.PurchaseOrderID WHERE (o.[OrderDate] >= '20021001' AND o.[OrderDate] < '20041001') GO

現(xiàn)在，數(shù)據(jù)已加載到分區(qū)表中，您可以使用新的內(nèi)置系統(tǒng)函數(shù)來(lái)確定數(shù)據(jù)所在的分區(qū)。下面的查詢很有用，因?yàn)樗鼘⒎祷匕瑪?shù)據(jù)的每個(gè)分區(qū)的以下信息：每個(gè)分區(qū)內(nèi)存在的行數(shù)以及最小和最大 OrderDate。此查詢不會(huì)返回不包含行的分區(qū)。

SELECT $partition.TwoYearDateRangePFN(o.OrderDate) AS [Partition Number] , min(o.OrderDate) AS [Min Order Date] , max(o.OrderDate) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.Orders AS o GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate) ORDER BY [Partition Number] GO SELECT $partition.TwoYearDateRangePFN(od.OrderDate) AS [Partition Number] , min(od.OrderDate) AS [Min Order Date] , max(od.OrderDate) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.OrderDetails AS od GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate) ORDER BY [Partition Number] GO

最后，在填充表后，可以建立群集索引。在本例中，群集索引將根據(jù)主鍵進(jìn)行定義，因?yàn)榉謪^(qū)鍵標(biāo)識(shí)兩個(gè)表（對(duì)于 OrderDetails，在索引中添加 LineNumber 以確保唯一性）。為分區(qū)表建立索引的默認(rèn)行為是將索引與同一架構(gòu)中的分區(qū)表對(duì)齊，而該架構(gòu)是不需要指定的。

ALTER TABLE Orders ADD CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED (OrderDate, OrderID) GO ALTER TABLE dbo.OrderDetails ADD CONSTRAINT OrderDetailsPK PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber) GO

指定分區(qū)架構(gòu)的完整語(yǔ)法如下：

ALTER TABLE Orders ADD CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED (OrderDate, OrderID) ON TwoYearDateRangePScheme(OrderDate) GO ALTER TABLE dbo.OrderDetails ADD CONSTRAINT OrderDetailsPK PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber) ON TwoYearDateRangePScheme(OrderDate) GO

連接分區(qū)表

連接對(duì)齊的表時(shí)，SQL Server 2005 提供了通過(guò)一個(gè)或多個(gè)步驟連接表的選項(xiàng)，通過(guò)此選項(xiàng)，可以先連接各個(gè)分區(qū)，然后將子集加起來(lái)。不管如何連接分區(qū)，SQL Server 都會(huì)評(píng)估是否可以實(shí)現(xiàn)某種程度的分區(qū)消除。

分區(qū)消除

在下面的查詢中，數(shù)據(jù)是從上一個(gè)方案中創(chuàng)建的 Orders 和 OrderDetails 表中查詢的。該查詢將只返回第三個(gè)季度的信息。通常，第三個(gè)季度包含訂單處理較慢的月份，但在 2004 年，這些月份是訂單最多的一些月份。在本例中，我們關(guān)心的是第三季度的 Products 趨勢(shì)（訂購(gòu)的數(shù)量及其訂單日期）。為了確保連接對(duì)齊的分區(qū)表時(shí)能夠受益于分區(qū)消除，必須指定每個(gè)表的分區(qū)范圍。在本例中，因?yàn)?Orders 表的主鍵是 OrderDate 和 OrderID 的組合鍵，這些表之間的連接顯示表之間的 OrderDate 必須相等。SARG（搜索參數(shù)）將應(yīng)用于兩個(gè)分區(qū)表。檢索此數(shù)據(jù)的查詢?nèi)缦?#xff1a;

SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty FROM dbo.Orders AS o INNER JOIN dbo.OrderDetails AS od ON o.OrderID = od.OrderID AND o.OrderDate = od.OrderDate WHERE o.OrderDate >= '20040701' AND o.OrderDate <= '20040930 11:59:59.997' GO

如圖 14 所示，查看實(shí)際或預(yù)測(cè)的示例輸出時(shí)，要查看一些關(guān)鍵元素：首先（使用 SQL Server Management Studio），將光標(biāo)懸停在所訪問(wèn)的表上時(shí)，您會(huì)看到“Estimated Number of Executions”或“Number of Executions”。在本例中，可以看到一個(gè)季度或三個(gè)月的數(shù)據(jù)。每個(gè)月都有自己的分區(qū)，而且查看此數(shù)據(jù)時(shí)可以看到執(zhí)行了三次：每個(gè)表一次。

點(diǎn)擊查看大圖

圖 14：執(zhí)行次數(shù)

如圖 15 所示，SQL Server 正在消除所有不需要的分區(qū)，并且只選擇包含正確數(shù)據(jù)的分區(qū)。請(qǐng)查看“Argument”部分中的 PARTITION ID:([PtnIds1017])，了解正在評(píng)估的內(nèi)容。您可能想知道“PtnIds1017”表達(dá)式是從哪里來(lái)的。這是此查詢中訪問(wèn)的分區(qū)的邏輯表示。如果您將光標(biāo)懸停在示例頂部的“Constant Scan”上，您會(huì)發(fā)現(xiàn)它顯示了參數(shù) VALUES(((21)), ((22)), ((23)))。這代表分區(qū)號(hào)。

圖 15：分區(qū)消除

要驗(yàn)證每個(gè)分區(qū)并且只驗(yàn)證這些分區(qū)中存在的數(shù)據(jù)，請(qǐng)使用前面使用的查詢（經(jīng)過(guò)稍微修改）訪問(wèn)分區(qū)的新的內(nèi)置系統(tǒng)函數(shù)：

此時(shí)，您可以從圖形上識(shí)別分區(qū)消除。可以對(duì)分區(qū)表和索引使用其他的優(yōu)化方法，尤其是在它們與您要連接的表對(duì)齊的情況下。SQL Server 可以通過(guò)先連接每個(gè)分區(qū)來(lái)執(zhí)行多個(gè)連接。

預(yù)先連接對(duì)齊的表

在同一個(gè)查詢中，SQL Server 不僅消除分區(qū)，還分別在其余分區(qū)之間執(zhí)行連接。除了查看每個(gè)表訪問(wèn)的執(zhí)行次數(shù)之外，請(qǐng)注意與合并連接相關(guān)的信息。如果將光標(biāo)懸停在合并連接的上方，您會(huì)發(fā)現(xiàn)合并連接執(zhí)行了三次。

圖 16：連接分區(qū)表

在圖 16 中，請(qǐng)注意執(zhí)行了額外的嵌套環(huán)連接。看上去這是在合并連接后發(fā)生的，但實(shí)際上，分區(qū) ID 已經(jīng)傳遞給每個(gè)表搜索或掃描操作；最后這個(gè)連接只是將兩個(gè)分區(qū)數(shù)據(jù)集連接起來(lái)，確保每個(gè)數(shù)據(jù)集都符合一開(kāi)始（在“Constant Scan”表達(dá)式中）定義的分區(qū) ID。

滑動(dòng)窗口方案

當(dāng)下一個(gè)月的數(shù)據(jù)（在本例中是 2004 年 10 月）可用時(shí)，將按特定的操作順序使用現(xiàn)有的文件組、移入和移出數(shù)據(jù)。而在本銷(xiāo)售方案中，目前 FG1 中的數(shù)據(jù)是 2002 年 10 月的數(shù)據(jù)。現(xiàn)在 2004 年 10 月的數(shù)據(jù)是可用的，因此，根據(jù)可用空間和存檔要求，您有兩個(gè)選擇。記住，要將分區(qū)從表中快速移入或移出，移動(dòng)操作必須只更改元數(shù)據(jù)。特別是，必須在要移入或移出的同一個(gè)文件組中創(chuàng)建新表（源或目標(biāo)，即偽造的分區(qū)）。如果您打算繼續(xù)使用相同的文件組（本例中為 FG1），則需要確定如何滿足空間和存檔要求。當(dāng)表中沒(méi)有完整的兩個(gè)年度的數(shù)據(jù)時(shí)，為了最大程度地縮短時(shí)間，并且如果擁有足夠的空間，您可以將當(dāng)前數(shù)據(jù)（2004 年 10 月）加載到 FG1 中，而無(wú)需刪除要存檔的數(shù)據(jù)（2002 年 10 月）。但是，如果沒(méi)有足夠的空間同時(shí)保留當(dāng)前月份和要存檔的月份，則需要先移出舊的分區(qū)（然后再刪除它）。

不管怎樣，存檔應(yīng)該很容易，并且可能已經(jīng)完成。好的存檔做法是，加載和移入新分區(qū)之后立即備份文件組，而不要等到打算移出分區(qū)時(shí)再進(jìn)行備份。例如，如果 RAID 陣列出現(xiàn)故障，則可以恢復(fù)文件組，而無(wú)需重新生成或重新加載數(shù)據(jù)。具體到本例中，因?yàn)閿?shù)據(jù)庫(kù)是最近才分區(qū)的，所以您可能已經(jīng)在分區(qū)結(jié)構(gòu)穩(wěn)定后執(zhí)行了完整的備份。當(dāng)然，完整的數(shù)據(jù)庫(kù)備份并非唯一的選擇。在 SQL Server 2005 中可以實(shí)現(xiàn)各種各樣的備份策略，而且許多備份策略都可以為備份和恢復(fù)提供更好的準(zhǔn)確性。因?yàn)檫@么多的數(shù)據(jù)都是不變的，所以您可以在加載后備份各個(gè)文件組。實(shí)際上，這應(yīng)該是滾動(dòng)分區(qū)策略的一部分。有關(guān)詳細(xì)信息，請(qǐng)參見(jiàn) SQL Server Books Online的“Administering SQL Server”中的“File and Filegroup Backups”部分。

現(xiàn)在，策略已經(jīng)就位，您需要了解確切的處理過(guò)程和語(yǔ)法。語(yǔ)法和步驟數(shù)可能看起來(lái)很復(fù)雜，但每個(gè)月的處理過(guò)程都是相同的。通過(guò)使用動(dòng)態(tài) SQL 執(zhí)行，您可以按照以下步驟輕松地使此過(guò)程自動(dòng)化：

?	管理將要移入的分區(qū)的分段表。
?	管理將要移出的分區(qū)的第二個(gè)分段表。
?	將舊數(shù)據(jù)移出分區(qū)表，并將新數(shù)據(jù)移入分區(qū)表。
?	刪除分段表。
?	備份文件組。

后面各節(jié)詳細(xì)介紹了每個(gè)步驟的語(yǔ)法和最佳做法，還提供了注釋，以幫助您通過(guò)動(dòng)態(tài) SQL 執(zhí)行使此過(guò)程自動(dòng)化。

管理將要移入的分區(qū)的分段表

1.	創(chuàng)建分段表（將來(lái)的偽造分區(qū)）。這個(gè)分段表必須有一個(gè)約束，將其數(shù)據(jù)限制為只對(duì)要?jiǎng)?chuàng)建的分區(qū)有效的數(shù)據(jù)。為了獲取更好的性能，將數(shù)據(jù)加載到未建立索引且未應(yīng)用約束的堆中，然后在將表移入分區(qū)表之前添加約束（參見(jiàn)步驟 3）WITH CHECK。 CREATE TABLE SalesDB.[dbo].[OrdersOctober2004]([OrderID] [int] NOT NULL,[EmployeeID] [int] NULL,[VendorID] [int] NULL,[TaxAmt] [money] NULL,[Freight] [money] NULL,[SubTotal] [money] NULL,[Status] [tinyint] NOT NULL,[RevisionNumber] [tinyint] NULL,[ModifiedDate] [datetime] NULL,[ShipMethodID] [tinyint] NULL,[ShipDate] [datetime] NOT NULL,[OrderDate] [datetime] NOT NULL,[TotalDue] [money] NULL) ON [FG1]GO 在自動(dòng)化過(guò)程中：此表很容易創(chuàng)建，因?yàn)樗偸谴懋?dāng)前月份。根據(jù)進(jìn)程運(yùn)行的時(shí)間，使用 DATENAME(m, getdate()) 等內(nèi)置函數(shù)檢測(cè)月份是很容易的。因?yàn)楸淼慕Y(jié)構(gòu)必須與現(xiàn)有表相匹配，所以每個(gè)月的主要變動(dòng)是表名稱(chēng)。但是，您可以為每個(gè)月使用相同的名稱(chēng)，因?yàn)閷⒈硖砑拥椒謪^(qū)中之后，表即不需要再存在。雖然將數(shù)據(jù)移入分區(qū)表后該表仍然存在，但您可以在移動(dòng)操作完成后刪除分段表。另外，必須更改日期范圍。因?yàn)槟幚淼氖?datetime 數(shù)據(jù)，而在時(shí)間的存儲(chǔ)方式方面又存在舍入問(wèn)題，所以必須能夠通過(guò)編程方式確定正確的毫秒值。要確定月末最后的 datetime 值，最容易的方法是將正在處理的月份加上 1 個(gè)月，然后再減去 2 或 3 毫秒。不能只減去 1 毫秒，因?yàn)?59.999 會(huì)上舍入為 .000，即下個(gè)月的第一天。可以減去 2 或 3 毫秒，因?yàn)?2 毫秒將向下舍入為 .997，而 3 毫秒等于 .997；.997 是可以存儲(chǔ)的有效值。這樣即可確定 datetime 范圍的正確結(jié)束值： DECLARE @Month nchar(2),@Year nchar(4),@StagingDateRange nchar(10)SELECT @Month = N'11', @Year = N'2004'SELECT @StagingDateRange = @Year + @Month + N'01'SELECT dateadd(ms, -2, @StagingDateRange) 每個(gè)月會(huì)重新創(chuàng)建表，因?yàn)樗枰Ａ粼谝迫牒鸵瞥鰯?shù)據(jù)的文件組中。要確定要處理的相應(yīng)文件組，請(qǐng)將以下系統(tǒng)表查詢與前面介紹的 $partition 函數(shù)結(jié)合使用。指定要移出的范圍內(nèi)的任何日期。這是要在其中執(zhí)行所有操作的分區(qū)和文件組。帶有下劃線的部分需要針對(duì)特定的表、分區(qū)函數(shù)和特定的日期進(jìn)行更改。 SELECT ps.name AS PSName,dds.destination_id AS PartitionNumber,fg.name AS FileGroupNameFROM (((sys.tables AS tINNER JOIN sys.indexes AS iON (t.object_id = i.object_id))INNER JOIN sys.partition_schemes AS psON (i.data_space_id = ps.data_space_id))INNER JOIN sys.destination_data_spaces AS ddsON (ps.data_space_id = dds.partition_scheme_id))INNER JOIN sys.filegroups AS fgON dds.data_space_id = fg.data_space_idWHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) ANDdds.destination_id = $partition.TwoYearDateRangePFN('20021001')
2.	加載包含數(shù)據(jù)的分段表。如果文件是一致的，此過(guò)程應(yīng)該通過(guò) BULK INSERT 語(yǔ)句執(zhí)行。在自動(dòng)化過(guò)程中：這是自動(dòng)化過(guò)程最復(fù)雜的部分。您需要確保所有文件都已經(jīng)加載，還應(yīng)考慮并行加載這些文件。跟蹤加載了哪些文件以及文件位置的表可以幫助您控制此過(guò)程。您可以創(chuàng)建一個(gè) SQL Agent 作業(yè)，每隔幾分鐘檢查一次文件，拾取新文件并執(zhí)行多個(gè) BULK INSERT 語(yǔ)句。
3.	加載數(shù)據(jù)后，即可添加約束。為了使數(shù)據(jù)可信，必須添加約束 WITH CHECK。WITH CHECK 設(shè)置是默認(rèn)的，因此不需要指定，但一定不能設(shè)置為 WITH NOCHECK。
4.	為分段表建立索引。分段表必須與其要移入的表（成為該表的一個(gè)分區(qū)）具有相同的群集索引。 ALTER TABLE [OrdersOctober2004]ADD CONSTRAINT OrdersOctober2004PKPRIMARY KEY CLUSTERED (OrderDate, OrderID)ON [FG1]GO 在自動(dòng)化過(guò)程中：這是一個(gè)非常容易的步驟。使用步驟 1 中的月份和文件組信息，可以創(chuàng)建此群集索引。 ALTER TABLE SalesDB.[dbo].[OrdersOctober2004]WITH CHECKADD CONSTRAINT OrdersRangeYearCKCHECK ([OrderDate] >= '20041001'AND [OrderDate] <= '20041031 23:59:59.997')GO

管理將要移出的分區(qū)的第二個(gè)分段表。

創(chuàng)建第二個(gè)分段表。這是一個(gè)空表，用于存儲(chǔ)移出的分區(qū)中的數(shù)據(jù)。

CREATE TABLE SalesDB.[dbo].[OrdersOctober2002]([OrderID] [int] NOT NULL,[EmployeeID] [int] NULL,[VendorID] [int] NULL,[TaxAmt] [money] NULL,[Freight] [money] NULL,[SubTotal] [money] NULL,[Status] [tinyint] NOT NULL,[RevisionNumber] [tinyint] NULL,[ModifiedDate] [datetime] NULL,[ShipMethodID] [tinyint] NULL,[ShipDate] [datetime] NOT NULL,[OrderDate] [datetime] NOT NULL,[TotalDue] [money] NULL) ON [FG1]GO

為分段表建立索引。分段表必須與其要移入的表（成為該表的一個(gè)分區(qū)，而該分區(qū)將成為此表）具有相同的群集索引。

ALTER TABLE [OrdersOctober2002]ADD CONSTRAINT OrdersOctober2002PKPRIMARY KEY CLUSTERED (OrderDate, OrderID)ON [FG1]GO

將舊數(shù)據(jù)移出分區(qū)表，并將新數(shù)據(jù)移入分區(qū)表

1.	移出舊數(shù)據(jù)，放入第二個(gè)分段表中。 ALTER TABLE OrdersSWITCH PARTITION 1TO OrdersOctober2002GO
2.	更改分區(qū)函數(shù)以刪除 2002 年 10 月的邊界點(diǎn)。 ALTER PARTITION FUNCTION TwoYearDateRangePFN()MERGE RANGE ('20021031 23:59:59.997')GO
3.	此操作還會(huì)刪除文件組與分區(qū)架構(gòu)之間的關(guān)聯(lián)。具體來(lái)說(shuō)，FG1 將不再是分區(qū)架構(gòu)的一部分。因?yàn)槟鷮L動(dòng)相同的現(xiàn)有 24 個(gè)分區(qū)的新數(shù)據(jù)，所以需要使 FG1 成為“下一個(gè)使用的”分區(qū)，此分區(qū)將是下一個(gè)用于拆分的分區(qū)。 ALTER PARTITION SCHEME TwoYearDateRangePSchemeNEXT USED [FG1]GO
4.	更改分區(qū)函數(shù)，為 2004 年 10 月添加新的邊界點(diǎn)。 ALTER PARTITION FUNCTION TwoYearDateRangePFN()SPLIT RANGE ('20041031 23:59:59.997')GO
5.	更改基礎(chǔ)表的約束定義（如果存在），以允許新范圍的數(shù)據(jù)。因?yàn)樘砑蛹s束的代價(jià)可能很昂貴（需要驗(yàn)證數(shù)據(jù)），所以最好的做法是繼續(xù)擴(kuò)大日期范圍，而不是刪除并重新創(chuàng)建約束。現(xiàn)在，只存在一個(gè)約束 (OrdersRangeYearCK)，但以后將存在兩個(gè)約束。 ALTER TABLE OrdersADD CONSTRAINT OrdersRangeMaxOctober2004CHECK ([OrderDate] < '20041101')GOALTER TABLE OrdersADD CONSTRAINT OrdersRangeMinNovember2002CHECK ([OrderDate] >= '20021101')GOALTER TABLE OrdersDROP CONSTRAINT OrdersRangeYearCKGO
6.	從第一個(gè)分段表中移入新數(shù)據(jù)。 ALTER TABLE OrdersOctober2004SWITCH TO Orders PARTITION 24GO

刪除分段表

因?yàn)橄乱粋€(gè)步驟（也是最后一個(gè)步驟）將存檔所有數(shù)據(jù)，所以不再需要分段數(shù)據(jù)。刪除表是最快的方式。

DROP TABLE dbo.OrdersOctober2002 GO DROP TABLE dbo.OrdersOctober2004 GO

備份文件組

最后一步備份的對(duì)象是根據(jù)您的備份策略選擇的。如果選擇了基于文件或文件組的備份策略，則應(yīng)執(zhí)行文件或文件組備份。如果選擇了基于整個(gè)數(shù)據(jù)庫(kù)的備份策略，則可以執(zhí)行完整數(shù)據(jù)庫(kù)備份或差異備份。

BACKUP DATABASE SalesDB FILEGROUP = 'FG1' TO DISK = 'C:\SalesDB\SalesDB.bak' GO

列表分區(qū)：地區(qū)數(shù)據(jù)

如果您的表包含的數(shù)據(jù)來(lái)自多個(gè)地區(qū)，而通常只對(duì)一個(gè)地區(qū)的數(shù)據(jù)進(jìn)行分析，或者定期從每個(gè)地區(qū)接收數(shù)據(jù)，請(qǐng)考慮以列表的形式使用已定義的范圍分區(qū)。換句話說(shuō)，就是使用函數(shù)將每個(gè)分區(qū)定義為一個(gè)地區(qū)值。例如，有一家西班牙的公司，它的客戶分布在西班牙、法國(guó)、德國(guó)、意大利和英國(guó)。該公司的銷(xiāo)售數(shù)據(jù)總是按國(guó)家進(jìn)行分析。該公司的表可以擁有 5 個(gè)分區(qū)，每個(gè)國(guó)家一個(gè)分區(qū)。

這樣一個(gè)列表分區(qū)的創(chuàng)建過(guò)程與日期的范圍分區(qū)幾乎完全相同，不同之處在于，除了實(shí)際的分區(qū)鍵外，該范圍的邊界沒(méi)有任何其他值。實(shí)際上，它是一個(gè)列表，而不是范圍。盡管它是一個(gè)列表，但邊界條件必須包含最左側(cè)和最右側(cè)。要?jiǎng)?chuàng)建 5 個(gè)分區(qū)，只需在分區(qū)函數(shù)中指定 4 個(gè)值。不需要為這些值排序（SQL Server 將在內(nèi)部為它們排序），但是為了獲得正確的分區(qū)數(shù)，最符合邏輯的方法是對(duì)分區(qū)值進(jìn)行排序，將最大值指定給最后一個(gè)分區(qū)（定義為 LEFT 分區(qū)函數(shù)時(shí)），或者對(duì)分區(qū)值進(jìn)行排序，并從倒數(shù)第二個(gè)值開(kāi)始（對(duì)于 RIGHT）。

因?yàn)橛?5 個(gè)分區(qū)，所以必須有 5 個(gè)文件組。在本例中，這些文件組根據(jù)存儲(chǔ)的數(shù)據(jù)命名。腳本文件 RegionalRangeCaseStudyFilegroups.sql 充分顯示了此語(yǔ)法。每個(gè)文件組都可以使用相同的設(shè)置進(jìn)行創(chuàng)建，但如果數(shù)據(jù)不平衡，則不必這么做。只顯示了西班牙的文件組和文件；其他四個(gè)文件組和文件中都具有相同的參數(shù)，但位于不同的驅(qū)動(dòng)器上，而且具有特定于國(guó)家分區(qū)的名稱(chēng)。

ALTER DATABASE SalesDB ADD FILEGROUP [Spain] GO ALTER DATABASE SalesDB ADD FILE (NAME = N'SalesDBSpain', FILENAME = N'C:\SalesDB\SalesDBSpain.ndf', SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [Spain] GO

下一步是創(chuàng)建函數(shù)，該函數(shù)將指定只有四個(gè)分區(qū)使用 LEFT 作為邊界條件。在本例中，列表將包括除英國(guó)以外的所有國(guó)家，因?yàn)橛?guó)在按字母順序排列的列表中位于最后。

CREATE PARTITION FUNCTION CustomersCountryPFN(char(7)) AS RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain') GO

要將數(shù)據(jù)放入根據(jù)數(shù)據(jù)命名的文件組中，分區(qū)架構(gòu)將按字母順序列出。所有五個(gè)文件組都必須在分區(qū)架構(gòu)的語(yǔ)法中指定。

CREATE PARTITION SCHEME [CustomersCountryPScheme] AS PARTITION CustomersCountryPFN TO ([France], [Germany], [Italy], [Spain], [UK]) GO

最后，可以在新的 CustomersCountryPScheme 中創(chuàng)建 Customers 表。

CREATE TABLE [dbo].[Customers]( [CustomerID] [nchar](5) NOT NULL, [CompanyName] [nvarchar](40) NOT NULL, [ContactName] [nvarchar](30) NULL, [ContactTitle] [nvarchar](30) NULL, [Address] [nvarchar](60) NULL, [City] [nvarchar](15) NULL, [Region] [nvarchar](15) NULL, [PostalCode] [nvarchar](10) NULL, [Country] [char](7) NOT NULL, [Phone] [nvarchar](24) NULL, [Fax] [nvarchar](24) NULL ) ON CustomersCountryPScheme (Country) GO

盡管范圍分區(qū)被定義為只支持范圍，但它們還為執(zhí)行其他類(lèi)型的分區(qū)（例如列表分區(qū)）提供了一種方法。

總結(jié)

使用 SQL Server 2005，您可以通過(guò)分區(qū)輕松一致地管理大量表和索引，從而使您可以在活動(dòng)表之外管理數(shù)據(jù)子集。這提供了簡(jiǎn)化的管理、改善的性能以及抽象的應(yīng)用程序邏輯，因?yàn)榉謪^(qū)架構(gòu)對(duì)于應(yīng)用程序是完全透明的。如果您的數(shù)據(jù)具有邏輯分組（范圍或列表），而大量查詢必須分析這些預(yù)定義的、一致的范圍內(nèi)的數(shù)據(jù)，還需要管理這些預(yù)定義范圍內(nèi)的傳入數(shù)據(jù)和傳出數(shù)據(jù)，則范圍分區(qū)是簡(jiǎn)單的選擇。如果您查看對(duì)大量數(shù)據(jù)進(jìn)行的分析（沒(méi)有使用特定范圍），或者所有查詢都訪問(wèn)大多數(shù)數(shù)據(jù)（如果不是所有數(shù)據(jù)），那么使用多個(gè)沒(méi)有任何特定放置方法的文件組是一種更容易的解決方案，此解決方案還可以改善性能。

本文的腳本

本白皮書(shū)代碼示例中使用的腳本可以從 SQLServer2005PartitionedTables.zip 文件中找到。下面介紹了該壓縮文件中的每個(gè)文件。

RangeCaseStudyScript1-Filegroups.sql - 包括用于創(chuàng)建范圍分區(qū)表案例研究所需的文件組和文件的語(yǔ)法。此腳本是可以修改的，是您可以在更小的磁盤(pán)集上使用更小的文件（以 MB 而不是 GB 為單位）創(chuàng)建此示例。此外，它還包含通過(guò) INSERT...SELECT 語(yǔ)句導(dǎo)入數(shù)據(jù)的代碼，使您可以通過(guò)相應(yīng)的分區(qū)函數(shù)推測(cè)數(shù)據(jù)的位置。

RangeCaseStudyScript2-PartitionedTable.sql - 包括用于創(chuàng)建與范圍分區(qū)表案例研究相關(guān)的分區(qū)函數(shù)、分區(qū)架構(gòu)和范圍分區(qū)表的語(yǔ)法。此腳本還包含相應(yīng)的約束和索引。

RangeCaseStudyScript3-JoiningAlignedTables.sql - 包括說(shuō)明 SQL Server 為分區(qū)表提供的各種連接策略的查詢。

RangeCaseStudyScript4-SlidingWindow.sql - 包括與范圍分區(qū)表案例研究中按月進(jìn)行管理有關(guān)的語(yǔ)法和過(guò)程。在此腳本中，您將在 Orders 表中移入和移出數(shù)據(jù)。您還可以自行決定是否創(chuàng)建相同的過(guò)程，以便在 OrderDetails 表中移入和移出數(shù)據(jù)。提示：請(qǐng)參見(jiàn) RangeCaseStudyScript2 中為 OrderDetails 插入表和正確數(shù)據(jù)列而使用的 Insert 語(yǔ)句。

RegionalRangeCaseStudyFilegroups.sql - 包括用于創(chuàng)建地區(qū)性分區(qū)表案例研究所需的文件組和文件的語(yǔ)法。實(shí)際上，這是一個(gè)模擬列表分區(qū)架構(gòu)的范圍分區(qū)。

RegionalRangeCaseStudyPartitionedTable.sql - 包括用于創(chuàng)建與范圍分區(qū)表案例研究相關(guān)的分區(qū)函數(shù)、分區(qū)架構(gòu)和地區(qū)性分區(qū)表的語(yǔ)法。

總結(jié)

以上是生活随笔為你收集整理的分区sql2005的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

分区

上一篇： [MySQL优化案例]系列 -- OPT
下一篇：如何学习oracle