SQLSERVER将一个文件组的数据移动到另一个文件组
移动数据：
1、有表分区
2、没有表分区
我这里只讨论没有表分区的情况
例子
比如：你有三个文件组，其中一个是主文件组
测试脚本：
1 USE master
2 GO
3
4
5 IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test'))
6 DROP DATABASE [Test]
7
8 --1.创建数据库
9 CREATE DATABASE [Test]
10 GO
11
12 USE [Test]
13 GO
14
15
16 --2.向Test库加文件组
17 ALTER DATABASE [Test]
18 ADD FILEGROUP [FG_Test_Id_01]
19
20 ALTER DATABASE [Test]
21 ADD FILEGROUP [FG_Test_Id_02]
22
23
24
25 --3.向文件组加文件
26 ALTER DATABASE [Test]
27 ADD FILE
28 (NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
29 TO FILEGROUP [FG_Test_Id_01];
30
31 ALTER DATABASE [Test]
32 ADD FILE
33 (NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
34 TO FILEGROUP [FG_Test_Id_02];
35
36
37 --4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上
38 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01]
39 GO
40
41
42 --5.插入数据
43 INSERT INTO [dbo].[aa]
44 SELECT 1,REPLICATE('s',3000)
45 GO 500
46
47
48 --6.查询数据
49 SELECT * FROM [dbo].[aa]
50
51
52 --7.创建聚集索引在[FG_Test_Id_02]文件组上
53 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02]
54 GO
55
56
57 --8.我们查看一下文件组的逻辑文件名
58 EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname
59
60
61
62 --9.收缩一下FG_Test_Id_01文件组文件
63 DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)
64
65
66 --10.你可以选择drop掉聚集索引，也可以选择不drop掉聚集索引
67 DROP INDEX PK_ID ON [dbo].[aa]
68上面的脚本虽然简单，但是隐藏了非常多的知识点
知识点1：创建了两个文件组，现在数据库有三个文件组，包括主文件组，当你不指定任何参数的时候默认创建出来的数据文件是1MB大小
知识点2：插入数据，因为表是创建在[FG_Test_Id_01]文件组上，所以数据都会放在E:\FG_TestUnique_Id_01_data.ndf
1 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01]
2 GO
知识点3：创建聚集索引，其实这句话里面包含了几个动作，在E:\FG_TestUnique_Id_02_data.ndf文件上分配页面，并把aa表的数据
放进去E:\FG_TestUnique_Id_02_data.ndf文件，其实这里聚集索引成为了移动数据的中介，我在
SQLSERVER聚集索引与非聚集索引的再次研究（上）文章写到：聚集索引叶子节点就是数据，我们把聚集索引（一定要是聚集索引，非聚集索引不是）
建立在E:\FG_TestUnique_Id_02_data.ndf文件上实际上就是把数据页面和聚集索引页面移动到E:\FG_TestUnique_Id_02_data.ndf文件里
因为SQLSERVER是没有 ALTER TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 这种语法
就是说你一旦建表并且表中已经有数据之后，如果你要移动表数据，只能通过聚集索引这个中介来移动表数据
1 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02]
2 GO从下图可以看出数据都已经移动到E:\FG_TestUnique_Id_02_data.ndf文件上
知识点4：为什麽要加上WITH(ONLINE=ON)？？如果你的应用是不能停机的话，加上WITH(ONLINE=ON)就可以在线的创建索引
详情参考：CREATE INDEX (Transact-SQL)
1 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02]
2 GO
知识点5：为什麽要收缩[FG_Test_Id_01]文件组文件E:\FG_TestUnique_Id_01_data.ndf
因为数据已经移动到E:\FG_TestUnique_Id_02_data.ndf文件上了，既然已经移到E:\FG_TestUnique_Id_02_data.ndf文件上
为什麽E:\FG_TestUnique_Id_01_data.ndf文件还显示5MB大小？？
大家可以看一下这篇文章：聚集索引表插入数据和删除数据的方式是怎样的
我delete了数据，SQLSERVER却没有完全释放空间，其实这里移动数据到别的文件/文件组相当于delete了数据了
但是SQLSERVER并没有释放这些空间，所以我需要收缩一下FG_Test_Id_01文件组文件
1 --9.收缩一下FG_Test_Id_01文件组文件
2 DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)
知识点6：DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)
SHRINKFILE的单位是MB，上面的语句就是收缩到1MB大小，其实这里如果E:\FG_TestUnique_Id_01_data.ndf文件有数据的话
并且需要占用2MB大小的空间，那么您使用DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)这句话只能收缩到2MB大小
并不会收缩到1MB大小的，因为这些数据需要占用空间，你怎麽收缩都收缩不了的，不信的话您们可以测试一下
相关文章：
DBCC SHRINKFILE (Transact-SQL)
user database的initial size和dbcc shrinkfile
总结
虽然移动数据的动作比较简单，但是知识点挺多的，有些人只知道怎麽做，不知道为什么我觉得这样不好
注意：移动数据只能一张表一张表的移动，如果表里预先已经有聚集索引，需要先drop掉
移动之前查一下表中的数据在哪个文件组中
1 USE [Northwind]
2 GO
3 EXEC [sys].[sp_help] @objname = N'[dbo].[Categories]' -- nvarchar(776)
如有不对的地方，欢迎大家拍砖o(∩_∩)o
2014-1-19 补充：
有表分区的方法
大概有三种
先创建新的数据文件，文件组，分区方案，分区函数
例如创建三个新的数据文件和文件组，分区方案和分区函数对于这三个新的数据文件和文件组
旧表：无论已经分区或者还没有分区都适用下面三种方法：
方法一：建立中间表（新表），中间表建立在新的分区方案上，然后用insert into 新表 select * from 旧表的方法，
方法二：建立中间表，中间表建立在新的分区方案上，然后用switch to，因为分区方案有三个分区
那么，switch to只能够将表的全部数据切换到其中一个分区
USE Sales
GO
ALTER TABLE 旧表 SWITCH PARTITION 1 TO 新表 PARTITION 1
GO或者
USE Sales
GO
ALTER TABLE 旧表 SWITCH PARTITION 1 TO 新表 PARTITION 2
GO或者
USE Sales
GO
ALTER TABLE 旧表 SWITCH PARTITION 1 TO 新表 PARTITION 3
GO上面的三条语句就会把旧表的所有数据移动到新表的某个分区，至于移动到哪个分区由最后那个分区号来指定PARTITION 要移动到哪个分区
方法三：drop掉旧表的聚集索引（如果有），然后在旧表上创建一个聚集索引，创建聚集索引的时候指定新的分区方案
这样就会把表数据移动到新分区方案所在文件组，即新的数据文件里，这个方法跟没有表分区的数据移动是一样的
ALTER TABLE [dbo].[旧表] WITH NOCHECK ADD
CONSTRAINT [PK_tb] PRIMARY KEY CLUSTERED
(
[id]
) ON [ps_Product_Scheme_mTb]([id])--将主键创建到分区方案上
参考文章：
删除文件和删除文件组
--Transact-SQL
USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat3 ;
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4 ;
GO
--Transact-SQL
USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILEGROUP Test1FG1 ;
GO
删除数据文件，前提是数据文件里不能有数据
-- 备份数据库
backup database xx to disk =xx
--将BSDF2017数据文件的数据剪切到同一文件组下的其他数据文件，如果文件组下没有其他数据文件，则SHRINKFILE命令失败
DBCC SHRINKFILE(BSDF2017,EMPTYFILE )
--BSDF2017文件已经为空，可以删除
ALTER DATABASE AdventureWorks2012
REMOVE FILE BSDF2017 ;
