文件和文件组概念
关于文件与文件组，简单概括如下，详情请参考官方文档“数据库文件和文件组database files and filegroups”或更多相关资料：
数据文件概念：
每个sql server数据库至少包含两个作系统文件：一个数据文件（data file）和一个日志文件(log file)。数据文件包含数据和对象，例如表、索引、存储过程和视图….。日志文件包含恢复数据库所需的所有事务的相关数据。其实在sql server中，数据文件分为三类，分别为：
主数据文件
主数据文件包含数据库的启动信息，并指向数据库中的其他文件。 用户数据和对象可存储在此文件中，也可以存储在辅助数据文件中。每个数据库有一个主要数据文件。
主要数据文件的建议文件扩展名是 .mdf。
辅助数据文件
辅助数据文件是可选的，由用户定义并存储用户数据。 通过将每个文件放在不同的磁盘驱动器上，辅助数据文件可用于将数据分散到多个磁盘上。
另外，如果数据库超过了单个 windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。
辅助数据文件的建议文件扩展名是 .ndf。
事务日志文件
事务日志文件保存用于恢复数据库的日志信息。 每个数据库必须至少有一个日志文件。 事务日志的建议文件扩展名是 .ldf。
注意：虽然文件的扩展名是可以修改的，但强烈建议不要去改扩展名。
文件组概念：
文件组（file group），简单来说，就是数据文件的组合。为了便于分配和管理，可以将数据文件集合起来，放到文件组中。每个数据库有一个主要文件组（primary file group），这个是默认的。 此文件组包含主要数据文件和未放入其他文件组的所有次要文件。 可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。总体来说，文件组是一个逻辑概念，类似于oracle数据库的表空间，文件组与文件的关系就类似于oracle中表空间与文件的概念。
文件组分类：
主文件组（primary）
内存优化数据文件组（memory optimized data）
filestream 文件组(filestream)
用户自定义文件组（user-defined）
注意事项：primary 文件组是默认文件组，除非使用 alter database 语句进行了修改。另外，即使指定其它文件组为默认文件组。但系统对象和表仍然分配给 primary 文件组，而不是新的默认文件组。
文件组的优缺点
文件组是逻辑概念，一个文件组对应一个或多个文件。创建表的时候指定文件组即可（默认，指定primary文件组），这样可以隔离用户对文件的依赖，耦合性就要低很多。另外，使用多个文件组和文件不仅仅是为了分散io和提高性能，还有高可用性方面的原因。有关一个数据库应该包含几个文件或文件组[下面部分内容来自sql server中数据库文件的存放方式，文件和文件组]
数据库中使用多个文件或文件组在高可用性方面的好处包括：
· 某文件的io损坏，数据库还可以保证部分在线。
· 将索引和表分开存放，假如索引文件不在线，数据依然可以被访问。
· 历史数据和热数据分开，历史归档数据损坏，不影响热数据。
· 分开数据文件使得在灾难恢复时仅仅恢复部分数据从而缩短了宕机时间
· 数据库分为多个文件使得可以通过增加文件或移动文件的方式解决空间不足的问题
从用户的角度来说，创建对象时需要指定存储文件组的只有三种数据对象：表，索引和大对象(lob)。
使用文件组可以隔离用户对文件的依赖，使得用户仅仅针对文件组来建立表和索引，而不用关心实际磁盘中的文件的情况。当文件移动或修改时，由于用户建立的表和索引是建立在文件组上的，并不依赖具体文件，因此sql server可以放心的管理文件。
另外，使用文件组的方式来管理文件，可以使得同一文件组内的文件分布在不同的硬盘中，能够大大提供io性能。
sql server根据每个文件设置的初始大小和增量值自动分配新加入的空间，假设在同一文件a设置的大小为文件b的两倍，新增一个数据占用3页，则按比例将2页分配到文件a中，1页分配到文件b中
新增文件组
如下所示，创建两个文件组，db_data_groups用来存放数据（聚集索引），db_index_groups用来存放索引（非聚集索引）
--创建文件组db_index_groups
use [master]
go
alter database [yoursqldb] add filegroup [db_index_groups]
go
--创建文件组db_data_groups
use [master]
go
alter database [yoursqldb] add filegroup [db_data_groups]
go
--向文件组新增文件
use [master];
go
alter database [yoursqldb] add file ( name = n'yoursqldb_ind_01', filename = n'd:\sql_data\yoursqldb_ind_01.ndf' , size = 16gb , filegrowth = 256mb ) to filegroup [db_index_groups];
go
alter database [yoursqldb] add file ( name = n'yoursqldb_ind_02', filename = n'e:\sql_data\yoursqldb_ind_02.ndf' , size = 16gb , filegrowth = 256mb ) to filegroup [db_index_groups];
go
alter database [yoursqldb] add file ( name = n'yoursqldb_ind_03', filename = n'f:\sql_data\yoursqldb_ind_03.ndf' , size = 16gb , filegrowth = 256mb ) to filegroup [db_index_groups];
go
alter database [yoursqldb] add file ( name = n'yoursqldb_ind_04', filename = n'g:\sql_data\yoursqldb_ind_04.ndf' , size = 16gb , filegrowth = 256mb ) to filegroup [db_index_groups];
go
use [master];
go
alter database [yoursqldb] add file ( name = n'yoursqldb_data_01', filename = n'd:\sql_data\yoursqldb_data_01.ndf' , size = 26gb , filegrowth = 256mb ) to filegroup [db_data_groups];
go
alter database [yoursqldb] add file ( name = n'yoursqldb_data_02', filename = n'e:\sql_data\yoursqldb_data_02.ndf' , size = 26gb , filegrowth = 256mb ) to filegroup [db_data_groups];
go
alter database [yoursqldb] add file ( name = n'yoursqldb_data_03', filename = n'f:\sql_data\yoursqldb_data_03.ndf' , size = 26gb , filegrowth = 256mb ) to filegroup [db_data_groups];
go
alter database [yoursqldb] add file ( name = n'yoursqldb_data_04', filename = n'g:\sql_data\yoursqldb_data_04.ndf' , size = 26gb , filegrowth = 256mb ) to filegroup [db_data_groups];
go
文件组间移动数据
如何在sql server的文件组直接移动数据呢？关于这个问题呢，有时候也算简单，但是如果你没有踩过坑，不是一个老司机的话，建议你还是看一看下面内容。对于在文件组移动数据文件，分下面几种情况：
非分区表
1： 聚集索引表
1.1 表没有clob对象
方法：重建聚集索引和非聚集索引可以移动文件组。
1.2 表拥有clob对象
方法：重建表到新文件组，将数据导入。因为重建聚集索引，不会将lob对象移动到对应表空间。
2： 堆表
2.1 表没有clob对象
方法1：新建一个聚集索引，转移文件组后，删除聚集索引。
方法2：重建表到新文件组，导入数据。
2.1 表有clob对象
方法：重建表到新文件组，导入数据。
案例演示：我们要将数据库adventureworks2014中的表businessentityaddress 从文件组primary移动到文件组[db_data_groups]，在移动之前，我们先检查一下表businessentityaddress的情况，如下所示，这个表属于
上述情况的1.1。
declare @file_group_name nvarchar(32);
set @file_group_name ='primary'
select ds.name as dataspacename
,au.type_desc as allocationdesc
,au.total_pages*1.0 / 128 as [totalsize(mb)]
,au.used_pages*1.0 / 128 as [usedsize(mb)]
,au.data_pages*1.0 / 128 as [datasize(mb)]
,sch.name as schemaname
,obj.name as objectname
,obj.type_desc as objecttype
,idx.type_desc as indextype
,idx.name as indexname
into #tmp_tables
from sys.data_spaces as ds
inner join sys.allocation_units as au
on ds.data_space_id = au.data_space_id
inner join sys.partitions as pa
on (au.type in (1, 3)
and au.container_id = pa.hobt_id)
or
(au.type = 2
and au.container_id = pa.partition_id)
inner join sys.objects as obj
on pa.object_id = obj.object_id
inner join sys.schemas as sch
on obj.schema_id = sch.schema_id
left join sys.indexes as idx
on pa.object_id = idx.object_id
and pa.index_id = idx.index_id
where ds.name=@file_group_name
order by au.total_pages desc
select *
from #tmp_tables
where objecttype = 'user_table' and objectname='person'
order by [totalsize(mb)] desc;
select count(distinct objectname)
from #tmp_tables
where objecttype != 'system_table';
drop table #tmp_tables;
对于这种情况，我们用下面脚本生成重建索引的脚本（为什么要用脚本生成呢，实际情况中，可能有很多或大量的表需要移动表空间，此时脚本的效率就凸现出来了。）
--重新创建聚集索引，移动数据到文件组db_data_groups
select ' create ' +
case when i.is_unique = 1 then ' unique ' else '' end +
i.type_desc collate database_default +' index ' +
quotename(i.name) + ' on ' +
schema_name(t.schema_id)+'.'+ quotename(t.name) + ' ( ' +
keycolumns + ' ) ' +
isnull(' include ('+includedcolumns+' ) ','') +
isnull(' where '+i.filter_definition,'') + ' with ( ' +
case when i.is_padded = 1 then ' pad_index = on ' else ' pad_index = off ' end + ',' +
'fillfactor = '+convert(char(5),case when i.fill_factor = 0 then 100 else i.fill_factor end) + ',' +
-- default value
'sort_in_tempdb = off ' + ',' +
case when i.ignore_dup_key = 1 then ' ignore_dup_key = on ' else ' ignore_dup_key = off ' end + ',' +
case when st.no_recompute = 0 then ' statistics_norecompute = off ' else ' statistics_norecompute = on ' end + ',' +
-- default value
' drop_existing = on ' + ',' +
-- default value
' online = off ' + ',' +
case when i.allow_row_locks = 1 then ' allow_row_locks = on ' else ' allow_row_locks = off ' end + ',' +
case when i.allow_page_locks = 1 then ' allow_page_locks = on ' else ' allow_page_locks = off ' end + ' ) on [db_data_groups]; ' [createindexscript]
--,i.is_unique
from sys.indexes i
join sys.tables t on t.object_id = i.object_id
join (select * from (
select ic2.object_id , ic2.index_id ,
stuff((select ' , ' + c.name + case when max(convert(int,ic1.is_descending_key)) = 1 then ' desc ' else ' asc ' end
from sys.index_columns ic1
join sys.columns c
on c.object_id = ic1.object_id
and c.column_id = ic1.column_id
and ic1.is_included_column = 0
where ic1.object_id = ic2.object_id
and ic1.index_id = ic2.index_id
group by ic1.object_id,c.name,index_id
order by max(ic1.key_ordinal)
for xml path('')), 1, 2, '') keycolumns
from sys.index_columns ic2
group by ic2.object_id ,ic2.index_id) tmp3 )tmp4
on i.object_id = tmp4.object_id and i.index_id = tmp4.index_id
join sys.stats st on st.object_id = i.object_id and st.stats_id = i.index_id
join sys.data_spaces ds on i.data_space_id=ds.data_space_id
join sys.filegroups fg on i.data_space_id=fg.data_space_id
left join (select * from (
select ic2.object_id , ic2.index_id ,
stuff((select ' , ' + c.name
from sys.index_columns ic1
join sys.columns c
on c.object_id = ic1.object_id
and c.column_id = ic1.column_id
and ic1.is_included_column = 1
where ic1.object_id = ic2.object_id
and ic1.index_id = ic2.index_id
group by ic1.object_id,c.name,index_id
for xml path('')), 1, 2, '') includedcolumns
from sys.index_columns ic2
group by ic2.object_id ,ic2.index_id) tmp1
where includedcolumns is not null ) tmp2
on tmp2.object_id = i.object_id and tmp2.index_id = i.index_id
where i.type=1 and i.data_space_id=1
and t.name ='businessentityaddress'
and fg.name !='db_data_groups'
此时上面脚本会生成下面脚本
create unique clustered index [pk_businessentityaddress_businessentityid_addressid_addresstypeid]
on person.[businessentityaddress] ( businessentityid asc , addressid asc , addresstypeid asc )
with ( pad_index = off ,fillfactor = 100 ,sort_in_tempdb = off , ignore_dup_key = off , statistics_norecompute = off ,
drop_existing = on , online = off , allow_row_locks = on , allow_page_locks = on ) on [db_data_groups];
下面脚本生成对应表的非聚集索引，将其转移到表空间[db_index_groups]
with noclusttable as
(
select distinct
schema_name(so.schema_id) as 'schemaname' ,
object_name(so.object_id) as 'tablename' ,
so.object_id as 'object_id' ,
case objectproperty(max(so.object_id), 'tablehasclustindex')
when 0 then count(si.index_id) - 1
else count(si.index_id)
end as 'indexcount'
from sys.objects so ( nolock )
join sys.indexes si ( nolock ) on so.object_id = si.object_id
and so.type in ( n'u', n'v' )
join sysindexes dmv ( nolock ) on so.object_id = dmv.id
and si.index_id = dmv.indid
full outer join ( select object_id ,
count(1) as columncount
from sys.columns (nolock)
group by object_id
) d on d.object_id = so.object_id
where so.is_ms_shipped = 0
and so.object_id not in (
select major_id
from sys.extended_properties (nolock)
where name = n'microsoft_database_tools_support' )
and indexproperty(so.object_id, si.name, 'isstatistics') = 0
group by so.schema_id ,
so.object_id
having ( objectproperty(max(so.object_id), 'tablehasclustindex') = 0
and count(si.index_id) - 1 > 0
)
)
select ' create ' +
case when i.is_unique = 1 then ' unique ' else '' end +
i.type_desc collate database_default +' index ' +
quotename(i.name) + ' on ' +
schema_name(t.schema_id)+'.'+ quotename(t.name) + ' ( ' +
keycolumns + ' ) ' +
isnull(' include ('+includedcolumns+' ) ','') +
isnull(' where '+i.filter_definition,'') + ' with ( ' +
case when i.is_padded = 1 then ' pad_index = on ' else ' pad_index = off ' end + ',' +
'fillfactor = '+convert(char(5),case when i.fill_factor = 0 then 100 else i.fill_factor end) + ',' +
-- default value
'sort_in_tempdb = off ' + ',' +
case when i.ignore_dup_key = 1 then ' ignore_dup_key = on ' else ' ignore_dup_key = off ' end + ',' +
case when st.no_recompute = 0 then ' statistics_norecompute = off ' else ' statistics_norecompute = on ' end + ',' +
-- default value
' drop_existing = on ' + ',' +
-- default value
' online = off ' + ',' +
case when i.allow_row_locks = 1 then ' allow_row_locks = on ' else ' allow_row_locks = off ' end + ',' +
case when i.allow_page_locks = 1 then ' allow_page_locks = on ' else ' allow_page_locks = off ' end + ' ) on [db_index_groups]; ' [createindexscript]
--,i.is_unique
from sys.indexes i
join sys.tables t on t.object_id = i.object_id
--join sys.sysindexes si on i.object_id = si.id and i.index_id = si.indid
join (select * from (
select ic2.object_id , ic2.index_id ,
stuff((select ' , ' + c.name + case when max(convert(int,ic1.is_descending_key)) = 1 then ' desc ' else ' asc ' end
from sys.index_columns ic1
join sys.columns c
on c.object_id = ic1.object_id
and c.column_id = ic1.column_id
and ic1.is_included_column = 0
where ic1.object_id = ic2.object_id
and ic1.index_id = ic2.index_id
group by ic1.object_id,c.name,index_id
order by max(ic1.key_ordinal)
for xml path('')), 1, 2, '') keycolumns
from sys.index_columns ic2
group by ic2.object_id ,ic2.index_id) tmp3 )tmp4
on i.object_id = tmp4.object_id and i.index_id = tmp4.index_id
join sys.stats st on st.object_id = i.object_id and st.stats_id = i.index_id
join sys.data_spaces ds on i.data_space_id=ds.data_space_id
join sys.filegroups fg on i.data_space_id=fg.data_space_id
left join (select * from (
select ic2.object_id , ic2.index_id ,
stuff((select ' , ' + c.name
from sys.index_columns ic1
join sys.columns c
on c.object_id = ic1.object_id
and c.column_id = ic1.column_id
and ic1.is_included_column = 1
where ic1.object_id = ic2.object_id
and ic1.index_id = ic2.index_id
group by ic1.object_id,c.name,index_id
for xml path('')), 1, 2, '') includedcolumns
from sys.index_columns ic2
group by ic2.object_id ,ic2.index_id) tmp1
where includedcolumns is not null ) tmp2
on tmp2.object_id = i.object_id and tmp2.index_id = i.index_id
where t.object_id not in (
select major_id
from sys.extended_properties (nolock)
where name = n'microsoft_database_tools_support' )
and i.type = 2 and t.object_id not in (select object_id from noclusttable)
and i.data_space_id =1
and t.name='businessentityaddress'
create unique nonclustered index [ak_businessentityaddress_rowguid] on person.[businessentityaddress] ( rowguid asc ) with ( pad_index = off ,fillfactor = 100 ,sort_in_tempdb = off , ignore_dup_key = off , statistics_norecompute = off , drop_existing = on , online = off , allow_row_locks = on , allow_page_locks = on ) on [db_index_groups];
create nonclustered index [ix_businessentityaddress_addressid] on person.[businessentityaddress] ( addressid asc ) with ( pad_index = off ,fillfactor = 100 ,sort_in_tempdb = off , ignore_dup_key = off , statistics_norecompute = off , drop_existing = on , online = off , allow_row_locks = on , allow_page_locks = on ) on [db_index_groups];
create nonclustered index [ix_businessentityaddress_addresstypeid] on person.[businessentityaddress] ( addresstypeid asc ) with ( pad_index = off ,fillfactor = 100 ,sort_in_tempdb = off , ignore_dup_key = off , statistics_norecompute = off , drop_existing = on , online = off , allow_row_locks = on , allow_page_locks = on ) on [db_index_groups];
对于聚集索引表，如果拥有lob对象，那么上述方法不完全可行，我们以person.person为例，这个表有clob对象。执行上面操作后，你会发现lob_data 还是位于primary文件组。
这个是因为通过重建索引的方式是无法移动lob对象，如下所示，生成该表的创建脚本，你会发现textimage_on位于[primary]文件组， 这种情形，只能通过重建表，导入数据的方式。
个人在实际操作中，总结了一下简单步骤：
1：使用”generate and publish scripts“ 生成含有lob对象的表的脚本（具体细节有很多需要注意的地方），修改对应的文件组（例如将primary 改为[db_data_groups] 或[db_index_groups]）
2：确认表单独授权给了那些用户或角色（如果是默认角色，可以忽略），新建这些表必须考虑权限问题，否则重建这些表就会出现权限问题。
—查看某个对象被授予了那些用户或角色
select o.name as objectname,u.name as objname,
case o.type
when ‘af’ then ‘aggregate function(clr)’
when ‘c’ then ‘check constraint’
when ‘d’ then ‘default constraint’
when ‘f’ then ‘foreign key constraint’
when ‘fn’ then ‘scalar function’
when ‘fs’ then ‘assembly (clr) scalar-function’
when ‘ft’ then ‘assembly (clr) table-valued function’
when ‘it’ then ‘internal table’
when ‘k’ then ‘primary key or unique constraint’
when ‘u’ then ‘user table’
when ‘p’ then ‘stored procedure’
when ‘pc’ then ‘assembly (clr) stored procedure’
when ‘s’ then ‘system table’
when ‘sn’ then ‘synonym’
when ‘v’ then ‘view’
when ‘x’ then ‘extended store procedure’
when ‘tf’ then ‘table function’
else ‘other’
end as type,
case when p.action = 26 and p.protecttype = 205 then ‘√‘ else ” end as ‘references’,
case when p.action = 193 and p.protecttype = 205 then ‘√‘ else ” end as ‘select’,
case when p.action = 195 and p.protecttype = 205 then ‘√‘ else ” end as ‘delete’,
case when p.action = 196 and p.protecttype = 205 then ‘√‘ else ” end as ‘insert’,
case when p.action = 197 and p.protecttype = 205 then ‘√‘ else ” end as ‘update’,
case when p.action = 224 and p.protecttype = 205 then ‘√‘ else ” end as ‘execute’,
case p.protecttype
when 204 then ‘grant_w_grant ‘
when 205 then ‘grant’
when 206 then ‘deny’
else ‘other’
end as protecttype
from sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where o.name=‘person.person’ —具体表名
3：我们需要将原表重命名（包括约束, 如下所示，使用下面脚本生成对应的脚本，执行后修改表名或约束名称），然后执行步骤1里面生成的脚本，创建新表()
--use [adventureworks2014]
--go
declare @ori_tab_name nvarchar(64);
declare @old_tab_name nvarchar(64);
set @ori_tab_name = 'person.person';
set @old_tab_name = rtrim(@ori_tab_name) + '_old';
select 'sp_rename ''' + schema_name(schema_id) +'.' + name + ''' ,'''
+ name + '_old'';' + char(10) + 'go'
from sys.key_constraints
where parent_object_id = object_id(@ori_tab_name);
select 'sp_rename ''' + schema_name(schema_id) +'.' + name + ''', '''
+ name + '_old'';' + char(10) + 'go'
from sys.default_constraints
where parent_object_id = object_id(@ori_tab_name);
select 'sp_rename ''' + schema_name(schema_id) +'.' + name + ''', '''
+ name + '_old'';' + char(10) + 'go'
from sys.foreign_keys
where parent_object_id =object_id(@ori_tab_name);
select 'sp_rename ''' + schema_name(schema_id) + '.' + name +''', '''
+ name + '_old'';' + char(10) + 'go'
from sys.check_constraints
where parent_object_id =object_id(@ori_tab_name);
select 'sp_rename ''' +schema_name(schema_id) + '.' + name + ''', ''' + name + '_old'';'
from sys.tables
where object_id = object_id(@ori_tab_name);
注意：有外键约束指向这个表的，还需要额外调整。
insert into person.person
( businessentityid ,
persontype ,
namestyle ,
title ,
firstname ,
middlename ,
lastname ,
suffix ,
emailpromotion ,
additionalcontactinfo ,
demographics ,
rowguid ,
modifieddate
)
select businessentityid ,
persontype ,
namestyle ,
title ,
firstname ,
middlename ,
lastname ,
suffix ,
emailpromotion ,
additionalcontactinfo ,
demographics ,
rowguid ,
modifieddate
from person.person_old;
sp_spaceused 'person.person';
go
sp_spaceused 'person.person_old';
go
truncate table person.person_old;
go
drop table person.person_old;
go
如果遇到有自增字段，还必须考虑插入自增字段相关值。 总的来说，这样转移文件组是一个相当麻烦的事情。对于堆表，这里也没啥好介绍的。无法是细节问题需要注意。转移文件组倒没有什么技术含量。还有一个因素就是这样的操作会影响业务。
set identity_insert [dbo].[yourtablename] on;
go
insert into [dbo].[yourtablename](…………)
select ………….
go
set identity_insert [dbo].[yourtablename] off;
go
分区表
对于分区表而言，也可以通过重新创建聚集索引和非聚集索引来移动数据。另外，就是重新创建该表，转移数据。这个大同小异。当然，分区表还有切换分区（switch partition）这个机制来转移数据。
删除文件和文件组
删除文件前，必须确保文件为空，否则就会报“the file ‘xxxx’ cannot be removed because it is not empty.”,所以，首先必须转移文件当中的对象，然后收缩文件，最后删除文件。
dbcc shrinkfile (filename, emptyfile);
use [adventureworks2014]
go
alter database [adventureworks2014] remove file [adventureworks2014_data_01]
go
正常情况下，删除文件组对应的文件后，就可以删除文件组。
alter database [adventureworks2014] remove filegroup [db_data_groups]
go
但是如果存在分区表，你可能无法删除文件组。因为还有几个东西依赖文件组，一是分区方案，二是使用该分区方案的分区表。
所以要删除分区方案才能删除文件组。但要删除分区方案之前要先更改依赖它的分区表，使其不依赖它。 这个主要是更改分区表的分区列，使其不使用分区方案，如果实在不会更改，在表里数据已经备份的前提下，可以直接删除表来解决。 然后再删除分区表方案，最后就可以直接删除文件组了。
1、修改分区表，使其不依赖分区方案。
2、删除分区方案（依赖要删除的文件组）。
drop partition scheme [part_func_scheme_name]
3、直接删除文件组。
alter database [databasename] remove filegroup [xxxx]
参考资料：
https://www.sqlskills.com/blogs/paul/files-and-filegroups-survey-results/
https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017
http://www.cnblogs.com/careyson/archive/2011/12/26/2301597.html