How do I move data between filegroups in SQL Server?

Usually I don't know how many times I've been asked this question: "How do you move data between filegroups in SQL Server?" "You realize the problem: you have only one default configuration for the primary filegroup, and then after watching" Files and filegroups in SQL Server, "You know, a custom filegroup with multiple files would be a better idea. But how do you move existing data from the primary filegroup to the newly added filegroup now?

The purpose of this article is to show you how you can move data between filegroups. First I'll talk about the clustered and nonclustered indexes, and then I'll talk about how to move the data in the heap table. Let's get started!

moving clustered and nonclustered indexes

Generally there should be a clustered index on your table. With an existing clustered index it is easy to move the table data (that is, the clustered index) to different filegroups. The following code I created a simple clustered and nonclustered index for the table, and inserted nearly 800MB of test data into the table.

CREATE TABLEtesttable (IDINT IDENTITY(1,1)PRIMARY KEY  not NULL, SomeData1INT  not NULL, SomeData2CHAR( the))GO--Create a supporting non-clustered IndexCREATE nonclustered INDEXIdx_somedata1 ontesttable (SomeData1)GO--Insert around MB of dataDECLARE @i INT = 0 while(@i < 100000)BEGIN    INSERT  intotesttable (SomeData1, SOMEDATA2)VALUES(@i,REPLICATE('a', the))    SET @i += 1ENDGO

But you perform sp_help system stored procedures on the table, and you can see 2 indexes (clustered and nonclustered) in the primary filegroup.

sp_help TestTable

Suppose now I've made you believe that a custom filegroup with multiple files is a good idea, and you put it into action:

--Add A new file group to the databaseALTER DATABASEMultiplefilegroupsADDFILEGROUP CustomfilegroupGO--Add A new file to the previous created file groupALTER DATABASEMultiplefilegroupsADD FILE(NAME= 'CustomFile1', FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. SQL2012\MSSQL\DATA\CUSTOMFILE1.NDF', SIZE=1048576KB, FileGrowth=65536KB) toFILEGROUP CustomfilegroupGO--Add A new file to the previous created file groupALTER DATABASEMultiplefilegroupsADD FILE(NAME= 'CustomFile2', FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. SQL2012\MSSQL\DATA\CUSTOMFILE2.NDF', SIZE=1048576KB, FileGrowth=65536KB) toFILEGROUP CustomfilegroupGO

The problem now is that all of your data is still in the primary filegroup. How do you move them to the newly added filegroup? The answer to this question is simple: rebuild these indexes (clustered and nonclustered indexes) and specify the new filegroup as the target! Let's start with the clustered index (the index name is obtained from Sys.index):

SELECT *  from WHERE object_id = object_id ('testtable')

-- Move the Clustered Index into the newly created file group CREATE UNIQUE CLUSTERED INDEX  on testtable (ID)  with (    =on) on customfilegroupGO

When you execute sp_help again, you will see that SQL Server has already told the clustered index to move completely into different filegroups.

Now let's proceed with the nonclustered index:

-- Create a supporting non-clustered Index CREATE nonclustered INDEX  on testtable (SomeData1)  with (    =on) on customfilegroupGO

Finally, we can shrink the data file of the primary filegroup to reclaim the allocated space:

-- Shrink The MDF file in the PRIMARY file group DBCC Shrinkfile ('testdatabase'0)GO

Now when you plug in another 800MB of data, you can finally verify that the new assignment takes place in the newly added filegroup, and the primary filegroup is still small. Get!

Moving a heap table

This requires a bit of finesse if you want to move data from the heap table to a custom filegroup. The main problem is that SQL Server does not provide a way to move heap table data between filegroups.

So let's work this out: you create a clustered index on the heap table temporarily (it moves the data into the custom filegroup), and then you delete the clustered index to the heap table.

 --  Create a new Clustered Index on the Heap Table that moves the data into the custom file group  create  unique  clustered  index  idx_ci Span style= "color: #0000ff;" >on   TestTable (ID)  on   Customfilegroup  go  --  Drop the previous created Clustered Index again;-)  drop  index  idx_ci  testtable  go  

I know it's a little strange, but there's no more efficient way. Another method is to create a new heap table in a custom filegroup, move the data to the new heap table, delete the original heap table, and rename the new heap table. It's not a perfect solution ...

Summary

Moving data between filegroups can be simple or complex-depending on whether a clustered index exists. If you have a clustered index, you only need to rebuild the index in your custom filegroup. If you are working on a heap table, you will temporarily increase the clustered index (which moves the table data to another filegroup) and then delete the clustered index. It's not a perfect solution ...

Thanks for your attention!

original link

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

How do I move data between filegroups in SQL Server?