SQL Server裡在檔案組間如何移動資料？

標籤：

平常我不知道被問了幾次這樣的問題：“SQL  Server裡在檔案組間如何移動資料？“你意識到這個問題：你只有一個主檔案組的預設配置，後來圍觀了“SQL Server裡的檔案和檔案組”後，你知道，有多個檔案的自訂檔案組會是個更好的主意。但你現在如何從主檔案組裡移動現有資料到新加的檔案組？

這篇文章的目的是向你展示你如何在檔案組間移動資料。首先我會談下聚集和非叢集索引，然後我會談下如何在堆表裡移動資料。讓我們開始吧！

移動聚集和非叢集索引

一般來說在你的表上通常應該有一個叢集索引。有了現存的叢集索引就很容易移動表資料（即叢集索引）到不同的檔案組。下列代碼我為表建立了一個簡單的聚集和非叢集索引，並插入近800MB的測試資料到表。

CREATE TABLE TestTable(    ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,    SomeData1 INT NOT NULL,    SomeData2 CHAR(5000))GO-- Create a supporting Non-Clustered IndexCREATE NONCLUSTERED INDEX idx_SomeData1 ON TestTable(SomeData1)GO-- Insert around 800 MB of dataDECLARE @i INT = 0WHILE (@i < 100000)BEGIN    INSERT INTO TestTable (SomeData1, SomeData2)    VALUES (@i, REPLICATE(‘a‘, 5000))    SET @i += 1ENDGO

但你在表上執行sp_help的系統預存程序，你可以看到在主檔案組裡看到2個索引（叢集索引和非聚集蘇音）。

sp_help TestTable

假設現在我已經讓你相信一個有多個檔案的自訂檔案組是個好主意，並且你付諸行動了：

-- Add a new file group to the databaseALTER DATABASE MultipleFileGroups ADD FILEGROUP CustomFileGroupGO-- Add a new file to the previous created file groupALTER DATABASE MultipleFileGroups ADD FILE(    NAME = ‘CustomFile1‘,     FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\CustomFile1.ndf‘,    SIZE = 1048576KB,    FILEGROWTH = 65536KB) TO FILEGROUP CustomFileGroupGO-- Add a new file to the previous created file groupALTER DATABASE MultipleFileGroups ADD FILE(    NAME = ‘CustomFile2‘,     FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\CustomFile2.ndf‘,    SIZE = 1048576KB,    FILEGROWTH = 65536KB) TO FILEGROUP CustomFileGroupGO

現在的問題是現存的你的所有資料還在主檔案組。你如何移動它們到新加的檔案組？這個問題的答案非常簡單：重建這些索引（聚集和非叢集索引）即可，並且指定新加的檔案組作為目標！我們先從叢集索引開始（索引名稱從sys.index裡得到）：

SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(‘TestTable‘)

-- Move the Clustered Index into the newly created file groupCREATE UNIQUE CLUSTERED INDEX PK__TestTabl__3214EC27D9EE93A9 ON TestTable(ID)WITH(    DROP_EXISTING = ON)ON CustomFileGroupGO

當你再次執行sp_help，你會看到SQL Server已經講叢集索引完全移入不同的檔案組。

現在我們繼續處理非叢集索引：

-- Create a supporting Non-Clustered IndexCREATE NONCLUSTERED INDEX idx_SomeData1 ON TestTable(SomeData1)WITH(    DROP_EXISTING = ON)ON CustomFileGroupGO

最後，我們可以收縮主檔案組的資料檔案來回收已指派的空間：

-- Shrink the MDF file in the PRIMARY file groupDBCC SHRINKFILE (‘TestDatabase‘ , 0)GO

現在當你插入另一個800MB的資料，你最終可以驗證新分配在新加的檔案組裡發生，主檔案組還是很小。搞定！

移動堆表

如果你想從堆表移動資料到自訂的檔案組，這需要一點技巧。主要的問題是SQL Server不提供在檔案組間移動堆表資料的方法。

因此我們要變通下：你在堆表上臨時建立一個叢集索引（會把資料移入自訂檔案組），然後你刪除叢集索引恢複為堆表。

-- Create a new Clustered Index on the Heap table that moves the data into the custom file groupCREATE UNIQUE CLUSTERED INDEX idx_ci ON TestTable(ID)ON CustomFileGroupGO-- Drop the previous created Clustered Index again ;-)DROP INDEX idx_ci ON TestTableGO

我知道這樣有點奇怪，但沒有其他更高效的方法。另一個方法是在自訂檔案組裡建立新的堆表，移動資料到新的堆表，刪除原來的堆表，重新命名新的堆表。還不是一個完美的解決方案……

小結

在檔案組間移動資料可以簡單也可以複雜——取決於有沒有叢集索引存在。如果你有叢集索引，你只需要在自訂檔案組重建索引即可。如果你要處理堆表，你要臨時增加叢集索引（它會移動表資料到別的檔案組），然後刪除叢集索引。真的不是個完美的解決方案……

感謝關注！

原文連結

https://www.sqlpassion.at/archive/2016/09/26/how-to-move-data-between-file-groups-in-sql-server

SQL Server裡在檔案組間如何移動資料？