sql server如何重建索引到其它檔案組

       在日常工作中，我們發現很多實施案例中，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

最後的查詢結果第一行就是執行的命令！