在日常工作中，我们发现很多实施案例中，sql server的数据库数据与索引在一起。我见过一个客户的，他的数据库总共大小才60g，但索引与数据完全混在一起，从管理数据库的直觉来看，性能方面肯定有问题，所以我建议他们，不管怎么样，把索引与数据库分开，对性能是有好处的！但是sql server的索引，想要通过重建的方式，把数据与索引分开，并不是一件容易的事怀，在使用rebuild时，并不能增加文件组选项。后来研究发现，可以通过以下方式把数据与非聚簇索引分开，具体如下：
set nocount on
 
declare @index table
(
      object_id int,
      objectName sysname,
      index_id int,
      indexName sysname,
      fill_factor tinyint,
      allow_row_locks bit,
      allow_page_locks bit,
      is_padded bit,
      indexText varchar(max),
      indexTextEnd varchar(max)
)
 
declare @indexColumn table
(
      object_id int,
      index_id int,
      column_id int,
      index_column_id int,
      max_index_column_id int,
      is_descending_key bit,
      is_included_column bit,
      columnName varchar(255),
      indexText varchar(max) null
)
 
insert into @index
select
      i.object_id,
      object_name(i.object_id),
      i.index_id,
      i.name,
      fill_factor,
      allow_row_locks,
      allow_page_locks,
      is_padded,
      'CREATE NONCLUSTERED INDEX [' + i.name + '] ON [dbo].[' + object_name(i.object_id) + '] ' + char(13),
      'WITH (PAD_INDEX = ' +
            CASE WHEN is_padded = 1 THEN ' ON ' ELSE ' OFF ' END +
            ', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ' +
            CASE WHEN allow_row_locks = 1 THEN ' ON ' ELSE ' OFF ' END +
            ', ALLOW_PAGE_LOCKS = ' +
            CASE WHEN allow_page_locks = 1 THEN ' ON ' ELSE ' OFF ' END +
            CASE WHEN fill_factor > 0 THEN ', FILLFACTOR = ' + convert(varchar(3), fill_factor) ELSE '' END +
            ') ON [IndexFG];print('''+i.name+'    @    '+object_name(i.object_id)+''')' --+ CHAR(13) +  ' GO;'+ CHAR(13)     --注意标红的地方，这是新的文件组的名称
from sys.indexes i
where i.type = 2 and not exists(select 1 from sys.key_constraints kc where kc.name=i.name)
and objectproperty(i.object_id , 'IsUserTable') = 1
order by object_name(i.object_id), i.name
 
insert into @indexColumn
select
      i.object_id,
      i.index_id,
      ic.column_id,
      ic.index_column_id,
      max(ic.index_column_id) over (partition by      i.object_id, i.index_id, is_included_column),
      is_descending_key,
      is_included_column,
      '[' + c.name + ']',
      null
from @index i
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
order by i.object_id, i.index_id, ic.index_column_id
 
 
 
declare @fields varchar(max)
declare @object_id int, @index_id int
 
select @fields = null, @object_id = -1, @index_id = -1
 
update @indexColumn
set @fields = indexText =
      case when object_id = isnull(@object_id, object_id) and index_id = isnull(@index_id, index_id)
            then isnull(@fields + ', ', ' ') + columnName + case when is_descending_key = 0 then ' ASC' else ' DESC' end
            else columnName + case when is_descending_key = 0 then ' ASC' else ' DESC' end
            end,
      @object_id = case when object_id <> @object_id
            then object_id else @object_id end,
      @index_id = case when index_id <> @index_id
            then index_id else @index_id end
from @indexColumn
where is_included_column = 0
 
select @fields = null, @object_id = -1, @index_id = -1
 
update @indexColumn
set @fields = indexText =
      case when object_id = isnull(@object_id, object_id) and index_id = isnull(@index_id, index_id)
            then isnull(@fields + ', ', ' ') + columnName
            else columnName
            end,
      @object_id = case when object_id <> @object_id
            then object_id else @object_id end,
      @index_id = case when index_id <> @index_id
            then index_id else @index_id end
from @indexColumn
where is_included_column = 1
 
update @index
set indexText = i.indexText + '( ' + char(13) + char(9) + ic.indexText + char(13) + ') '
from @index i join @indexColumn ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
and ic.index_column_id = ic.max_index_column_id
and ic.is_included_column = 0
 
update @index
set indexText = i.indexText + 'INCLUDE ( ' + char(13) + char(9) + ic.indexText + char(13) + ') '
from @index i join @indexColumn ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
and ic.index_column_id = ic.max_index_column_id
and ic.is_included_column = 1
 
update @index
set indexText = indexText + indexTextEnd
from @index
 
select indexText, objectName, indexName
from @index

最后的查询结果第一行就是执行的命令！