SQL Server moves data for one filegroup to another filegroup

Experienced heroes can directly ignore this article ~

The problem is experienced people know how to do it, because our company's data volume is not very small demand, and do not know how to do the experiment

QQ Group today to help the Pineapple warrior, finally know how to do

I will not understand the question will certainly ask the end, before the forum asked this weak weak question, but because too weak, people casually replied

Then the owner is labeled as the answer ~

Move data:

1. Table partitioning

2, no table partition

I'm only talking about the absence of table partitions, and the table partitioning is not quite clear.

Example

For example: You have three filegroups, one of which is the primary filegroup

Test script:

Use master go IF EXISTS (SELECT * from sys.[    
       
Databases] WHERE [database_id]=db_id (' Test ')] DROP the database [test]--1. Creating databases Create DB [Test] Go   
       
Use [test] go--2. Create filegroup ALTER DATABASE [Test] ADD FILEGROUP [fg_test_id_01]   
ALTER DATABASE [test] ADD FILEGROUP [fg_test_id_02]--3. Create a file ALTER database [test]  ADD FILE (NAME = n ' fg_testunique_id_01_data ', FILENAME = N ' E:\FG_TestUnique_Id_01_data.ndf ', SIZE = 1MB, filegrowth =   
       
1MB) to FILEGROUP [fg_test_id_01]; ALTER DATABASE [Test] ADD FILE (NAME = n ' fg_testunique_id_02_data ', FILENAME = N ' E:\FG_TestUnique_Id_02_data.ndf ', siz   
       
       
E = 1MB, filegrowth = 1MB) to FILEGROUP [fg_test_id_02];   
       
       
--4. Create table where the data for this table is placed on the [FG_TEST_ID_01] filegroup CREATE TABLE AA (Id INT, CNAME NVARCHAR (4000)) on [fg_test_id_01] Go --5. Inserts data insert into [dbo].   
[AA] SELECT 1,replicate (' s', 3000 go 500--6. Query data SELECT * FROM [dbo]. [AA]--7. Creates a clustered index on the [FG_TEST_ID_02] filegroup, create CLUSTERED index pk_id on [dbo]. [AA] ([id]) with (Online=on) on [fg_test_id_02] go--8. Let's look at the logical file name of the filegroup, EXEC [SYS]. [sp_helpdb] @dbname = TEST-sysname--9. Shrink fg_test_id_01 Filegroup file DBCC shrinkfile (fg_testuniq ue_id_01_data,1)--10. You can choose to drop the clustered index, or you can choose not to drop the clustered Index drop index pk_id on [dbo].   
       
[AA] Use master go DROP DATABASE [Test]

The script above is simple, but it hides a lot of knowledge.