文章出處

平常我不知道被問了幾次這樣的問題：“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 Index
CREATE NONCLUSTERED INDEX idx_SomeData1 ON TestTable(SomeData1)
GO

-- Insert around 800 MB of data
DECLARE @i INT = 0
WHILE (@i < 100000)
BEGIN
    INSERT INTO TestTable (SomeData1, SomeData2)
    VALUES (@i, REPLICATE('a', 5000))

    SET @i += 1
END
GO

但你在表上執行sp_help的系統存儲過程，你可以看到在主文件組里看到2個索引（聚集索引和非聚集蘇音）。

sp_help TestTable

假設現在我已經讓你相信一個有多個文件的自定義文件組是個好主意，并且你付諸行動了：

-- Add a new file group to the database
ALTER DATABASE MultipleFileGroups ADD FILEGROUP CustomFileGroup
GO

-- Add a new file to the previous created file group
ALTER 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 CustomFileGroup
GO

-- Add a new file to the previous created file group
ALTER 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 CustomFileGroup
GO

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

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

-- Move the Clustered Index into the newly created file group
CREATE UNIQUE CLUSTERED INDEX PK__TestTabl__3214EC27D9EE93A9 ON TestTable(ID)
WITH
(
    DROP_EXISTING = ON
)
ON CustomFileGroup
GO

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

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

-- Create a supporting Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_SomeData1 ON TestTable(SomeData1)
WITH
(
    DROP_EXISTING = ON
)
ON CustomFileGroup
GO

最后，我們可以收縮主文件組的數據文件來回收已分配的空間：

-- Shrink the MDF file in the PRIMARY file group
DBCC SHRINKFILE ('TestDatabase' , 0)
GO

現在當你插入另一個800MB的數據，你最終可以驗證新分配在新加的文件組里發生，主文件組還是很小。搞定！

移動堆表

如果你想從堆表移動數據到自定義的文件組，這需要一點技巧。主要的問題是SQL Server不提供在文件組間移動堆表數據的方法。

因此我們要變通下：你在堆表上臨時創建一個聚集索引（會把數據移入自定義文件組），然后你刪除聚集索引恢復為堆表。

-- Create a new Clustered Index on the Heap table that moves the data into the custom file group
CREATE UNIQUE CLUSTERED INDEX idx_ci ON TestTable(ID)
ON CustomFileGroup
GO

-- Drop the previous created Clustered Index again ;-)
DROP INDEX idx_ci ON TestTable
GO