Moving indexes from one filegroup to another in SQL Server

Article
09/26/2008

This is a pretty simple task but I would like to provide a generic method for doing this. It will help if the requirement is one (or all) of the folowing. I have only considered non-clustered indexes along with the Inclued columns and filtered indexes. No point in moving clustered indexes as moving them would move the data as well. This is because the leaf level of a clustered index is nothing but a data page.

1. Move all the Indexes of a Table/View

2. Move specific Indexes of a Table/View

2. Move all the indexes present in a Database

Here is a SP to achieve this and I haven't added validations as the idea is to present how to do this. Hope this is useful.

I am assuming that you have already created a user-defined FileGroup to which you would like to move the indexes. The required parameters for this SP are Database name, scehma name, FileGroup name and minimum of one table/view name for the @ObjectNameList parameter. If you would like to move indexes from more than one object, specify a comma separated string (Ex:- @ObjectNameList = 'Table1,Table2'). Specify the Index name argument only when you want to move a single index from that table/view.

CREATE PROC [dbo].[MoveIndexToFileGroup] (
@DBName sysname,
@SchemaName sysname = 'dbo',
@ObjectNameList Varchar(Max),
@IndexName sysname = null,
@FileGroupName varchar(100)
)
WITH RECOMPILE
AS

BEGIN

SET NOCOUNT ON

DECLARE @IndexSQL NVarchar(Max)
DECLARE @IndexKeySQL NVarchar(Max)
DECLARE @IncludeColSQL NVarchar(Max)
DECLARE @FinalSQL NVarchar(Max)

DECLARE @CurLoopCount Int
DECLARE @MaxLoopCount Int
DECLARE @StartPos Int
DECLARE @EndPos Int

DECLARE @ObjectName sysname
DECLARE @IndName sysname
DECLARE @IsUnique Varchar(10)
DECLARE @Type Varchar(25)
DECLARE @IsPadded Varchar(5)
DECLARE @IgnoreDupKey Varchar(5)
DECLARE @AllowRowLocks Varchar(5)
DECLARE @AllowPageLocks Varchar(5)
DECLARE @FillFactor Int
DECLARE @ExistingFGName Varchar(Max)
DECLARE @FilterDef NVarchar(Max)

DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @SQL nvarchar(4000)
DECLARE @RetVal Bit

DECLARE @ObjectList Table(Id Int Identity(1,1),ObjectName sysname)

DECLARE @WholeIndexData Table
(
ObjectName sysname
,IndexName sysname
,Is_Unique Bit
,Type_Desc Varchar(25)
,Is_Padded Bit
,Ignore_Dup_Key Bit
,Allow_Row_Locks Bit
,Allow_Page_Locks Bit
,Fill_Factor Int
,Is_Descending_Key Bit
,ColumnName sysname
,Is_Included_Column Bit
,FileGroupName Varchar(Max)
,Has_Filter Bit
,Filter_Definition NVarchar(Max)
)

DECLARE @DistinctIndexData Table
(
Id Int IDENTITY(1,1)
,ObjectName sysname
,IndexName sysname
,Is_Unique Bit
,Type_Desc Varchar(25)
,Is_Padded Bit
,Ignore_Dup_Key Bit
,Allow_Row_Locks Bit
,Allow_Page_Locks Bit
,Fill_Factor Int
,FileGroupName Varchar(Max)
,Has_Filter Bit
,Filter_Definition NVarchar(Max)
)

-------------Validate arguments----------------------

IF(@DBName IS NULL)
BEGIN
SELECT @ErrorMessage = 'Database Name must be supplied.'
GOTO ABEND
END

IF(@ObjectNameList IS NULL)
BEGIN
SELECT @ErrorMessage = 'Table or View Name(s) must be supplied.'
GOTO ABEND
END

IF(@FileGroupName IS NULL)
BEGIN
SELECT @ErrorMessage = 'FileGroup Name must be supplied.'
GOTO ABEND
END

--Check for the existence of the Database
IF NOT EXISTS(SELECT Name FROM sys.databases where Name = @DBName)
BEGIN
SET @ErrorMessage = 'The specified Database does not exist'
GOTO ABEND
END

--Check for the existence of the Schema
IF(upper(@SchemaName) <> 'DBO')
BEGIN
SET @SQL = 'SELECT @RetVal = COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.schemas WHERE name = ''' + @SchemaName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH
IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No Schema with the name ' + @SchemaName + ' exists in the Database ' + @DBName
GOTO ABEND
END
END

--Check for the existence of the FileGroup
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.filegroups WHERE name = ''' + @FileGroupName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH

IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No FileGroup with the name ' + @FileGroupName + ' exists in the Database ' + @DBName
GOTO ABEND
END

----------Get the objects from the concatenated list----------------------------------------------------

SET @StartPos = 0
SET @EndPos = 0

WHILE(@EndPos >= 0)
BEGIN

SELECT @EndPos = CHARINDEX(',',@ObjectNameList,@StartPos)
IF(@EndPos = 0) --Means, separator is not found
BEGIN
INSERT INTO @ObjectList
SELECT SUBSTRING(@ObjectNameList,@StartPos,(LEN(@ObjectNameList) - @StartPos)+1)

BREAK
END

INSERT INTO @ObjectList
SELECT SUBSTRING(@ObjectNameList,@StartPos,(@EndPos - @StartPos))

SET @StartPos = @EndPos + 1

END

-------------Check for the validity of all the Objects----------------------

SET @StartPos = 1
SELECT @EndPos = COUNT(*) FROM @ObjectList

WHILE(@StartPos <= @EndPos)
BEGIN

SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos

--Check for existence of the object
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE type IN (''U'',''V'') AND name = ''' + @ObjectName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH

IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No Table or View with the name ' + @ObjectName + ' exists in the Database ' + @DBName
GOTO ABEND
END

--Check for existence of Index
IF(@IndexName IS NOT NULL)
BEGIN
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Indexes si INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Objects so '
SET @SQL = @SQL + ' ON si.Object_Id = so.Object_Id WHERE so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25))
SET @SQL = @SQL + ' AND so.name = ''' + @ObjectName + ''' AND si.name = ''' + @IndexName + ''''

BEGIN TRY
   EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
   SELECT @ErrorMessage = ERROR_MESSAGE()
   GOTO ABEND
END CATCH

IF(@RetVal = 0)
BEGIN
   SELECT @ErrorMessage = 'No Index with the name ' + @IndexName + ' exists on the Object ' + @ObjectName
   GOTO ABEND
END
END

SET @StartPos = @StartPos + 1
END

-------------Loop till all the Objects are processed----------------------

SET @StartPos = 1
SELECT @EndPos = COUNT(*) FROM @ObjectList

WHILE(@StartPos <= @EndPos)
BEGIN

SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos

-------------Build the SQL to get the index data based on the inputs provided----------------------

SET @IndexSQL =
'SELECT so.Name as ObjectName, si.Name as IndexName,si.Is_Unique,si.Type_Desc'
+ ',si.Is_Padded,si.Ignore_Dup_Key,si.Allow_Row_Locks,si.Allow_Page_Locks,si.Fill_Factor,sic.Is_Descending_Key'
+ ',sc.Name as ColumnName,sic.Is_Included_Column,sf.Name as FileGroupName,si.Has_Filter,si.Filter_Definition FROM '
+ QUOTENAME(@DBName) + '.sys.Objects so INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Indexes si ON so.Object_Id = si.Object_id INNER JOIN '
+ QUOTENAME(@DBName) + '.sys.FileGroups sf ON sf.Data_Space_Id = si.Data_Space_Id INNER JOIN '
+ QUOTENAME(@DBName) + '.sys.Index_columns sic ON si.Object_Id = sic.Object_Id AND si.Index_id = sic.Index_id INNER JOIN '
+ QUOTENAME(@DBName) + '.sys.Columns sc ON sic.Column_Id = sc.Column_Id and sc.Object_Id = sic.Object_Id '
+ ' WHERE so.Name = ''' + @ObjectName + ''''
+ ' AND so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25)) + ' AND si.Type_Desc = ''NONCLUSTERED'' '

IF(@IndexName IS NOT NULL)
BEGIN
SET @IndexSQL = @IndexSQL + ' AND si.Name = ''' + @IndexName + ''''
END

SET @IndexSQL = @IndexSQL + ' ORDER BY ObjectName, IndexName'

--PRINT @IndexSQL

-------------Insert the Index Data in to a variable----------------------

BEGIN TRY
INSERT INTO @WholeIndexData
EXEC sp_executesql @IndexSQL
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH

--Check if any indexes are there on the object. Otherwise exit
IF (SELECT COUNT(*) FROM @WholeIndexData) = 0
BEGIN
SELECT 'Object does not have any nonclustered indexes to move'
GOTO FINAL
END

-------------Get the distinct index rows in to a variable----------------------

INSERT INTO @DistinctIndexData
SELECT DISTINCT
ObjectName,IndexName,Is_Unique,Type_Desc,Is_Padded,Ignore_Dup_Key,Allow_Row_Locks,Allow_Page_Locks,Fill_Factor,FileGroupName,Has_Filter,Filter_Definition
FROM @WholeIndexData
WHERE ObjectName = @ObjectName

SELECT @CurLoopCount = Min(Id), @MaxLoopCount = Max(Id) FROM @DistinctIndexData WHERE ObjectName = @ObjectName

--SELECT @CurLoopCount, @MaxLoopCount

-------------Loop till all the indexes are processed----------------------

WHILE(@CurLoopCount <= @MaxLoopCount)
BEGIN

SET @IndexKeySQL = ''
SET @IncludeColSQL = ''

-------------Get the current index row to be processed----------------------
SELECT
   @IndName   = IndexName
   ,@Type   = Type_Desc
   ,@ExistingFGName = FileGroupName
   ,@IsUnique  = CASE WHEN Is_Unique = 1 THEN 'UNIQUE ' ELSE '' END
   ,@IsPadded  = CASE WHEN Is_Padded = 0 THEN 'OFF,' ELSE 'ON,' END
   ,@IgnoreDupKey = CASE WHEN Ignore_Dup_Key = 0 THEN 'OFF,' ELSE 'ON,' END
   ,@AllowRowLocks = CASE WHEN Allow_Row_Locks = 0 THEN 'OFF,' ELSE 'ON,' END
   ,@AllowPageLocks = CASE WHEN Allow_Page_Locks = 0 THEN 'OFF,' ELSE 'ON,' END
   ,@FillFactor  = CASE WHEN Fill_Factor = 0 THEN 100 ELSE Fill_Factor END
   ,@FilterDef  = CASE WHEN Has_Filter = 1 THEN (' WHERE ' + Filter_Definition) ELSE '' END
FROM @DistinctIndexData
WHERE Id = @CurLoopCount

-------------Check if the index is already not part of that FileGroup----------------------

IF(@ExistingFGName = @FileGroupName)
BEGIN
   PRINT 'Index ' + @IndName + ' is NOT moved as it is already part of the FileGroup ' + @FileGroupName + '.'
   SET @CurLoopCount = @CurLoopCount + 1
   CONTINUE
END

------- Construct the Index key string along with the direction--------------------
SELECT
   @IndexKeySQL =
   CASE
   WHEN @IndexKeySQL = '' THEN (@IndexKeySQL + QUOTENAME(ColumnName) + CASE WHEN Is_Descending_Key = 0 THEN ' ASC' ELSE ' DESC' END)
   ELSE (@IndexKeySQL + ',' + QUOTENAME(ColumnName) + CASE WHEN Is_Descending_Key = 0 THEN ' ASC' ELSE ' DESC' END)
   END
FROM @WholeIndexData
WHERE ObjectName = @ObjectName
AND IndexName = @IndName
AND Is_Included_Column = 0

--PRINT @IndexKeySQL

------ Construct the Included Column string --------------------------------------
SELECT
   @IncludeColSQL =
   CASE
   WHEN @IncludeColSQL = '' THEN (@IncludeColSQL + QUOTENAME(ColumnName))
   ELSE (@IncludeColSQL + ',' + QUOTENAME(ColumnName))
   END
FROM @WholeIndexData
WHERE ObjectName = @ObjectName
AND IndexName = @IndName
AND Is_Included_Column = 1

--PRINT @IncludeColSQL

-------------Construct the final Create Index statement----------------------
SELECT
@FinalSQL = 'CREATE ' + @IsUnique + @Type + ' INDEX ' + QUOTENAME(@IndName)
+ ' ON ' + QUOTENAME(@DBName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)
+ '(' + @IndexKeySQL + ') '
+ CASE WHEN LEN(@IncludeColSQL) <> 0 THEN 'INCLUDE(' + @IncludeColSQL + ') ' ELSE '' END
+ @FilterDef
+ ' WITH ('
+ 'PAD_INDEX = ' + @IsPadded
+ 'IGNORE_DUP_KEY = ' + @IgnoreDupKey
+ 'ALLOW_ROW_LOCKS = ' + @AllowRowLocks
+ 'ALLOW_PAGE_LOCKS = ' + @AllowPageLocks
+ 'SORT_IN_TEMPDB = OFF,'
+ 'DROP_EXISTING = ON,'
+ 'ONLINE = OFF,'
+ 'FILLFACTOR = ' + CAST(@FillFactor AS Varchar(3))
+ ') ON ' + QUOTENAME(@FileGroupName)

--PRINT @FinalSQL

-------------Execute the Create Index statement to move to the specified filegroup----------------------
BEGIN TRY
   EXEC sp_executesql @FinalSQL
END TRY
BEGIN CATCH
   SELECT @ErrorMessage = ERROR_MESSAGE()
   GOTO ABEND
END CATCH
PRINT 'Index ' + @IndName + ' on Object ' + @ObjectName + ' is moved successfully.'

SET @CurLoopCount = @CurLoopCount + 1

END

SET @StartPos = @StartPos + 1
END
SELECT 'The procedure completed successfully.'
RETURN

ABEND:
RAISERROR 500001 @ErrorMessage
FINAL:
RETURN
END

Moving indexes from one filegroup to another in SQL Server

Additional resources