Skip to content

Instantly share code, notes, and snippets.

@dcs619
Forked from dalenewman/gist:6377911
Last active April 8, 2016 18:12
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dcs619/cd0a7682c4a33b06dc3fb9891e7f401e to your computer and use it in GitHub Desktop.
Save dcs619/cd0a7682c4a33b06dc3fb9891e7f401e to your computer and use it in GitHub Desktop.
A T-SQL stored procedure for moving an index from one file group to another. The original script was found at http://blogs.msdn.com/b/ramoji/archive/2008/09/26/how-to-move-existing-indexes-from-one-filegroup-to-another.aspx and updated according to responses found on the same page.
/*
-- See below for moving a non-clustered file index. Moving a clustered index is the same
-- as moving the data, as each row lives as a leaf somewhere in the index tree.
-- Current syntax for moving a CLUSTERED file index:
PRINT 'Moving PK_dbo.Group index'
CREATE UNIQUE CLUSTERED INDEX
[PK_dbo.Group]
ON
[Group] ( [Id] )
WITH (
DROP_EXISTING = ON,
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = ON,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [SECONDARY]
GO
*/
-- The following script generates index moves for clustered/non-clustered indexes as needed
;WITH CTE AS (
select ic.index_id + ic.object_id AS indexId
, t.name AS tableName
, i.name AS indexName
, c.name AS columnName
, i.type_desc COLLATE DATABASE_DEFAULT as indexType
, ic.is_included_column as columnIncludes
, ic.is_descending_key as is_descending
, i.is_unique
from sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
--and i.type_desc = 'NONCLUSTERED'
inner join sys.columns c
on ic.column_id = c.column_id
and i.object_id = c.object_id
inner join sys.tables t
on i.object_id = t.object_id
)
select N'
PRINT ''Moving ' + indexName + ' index on ' + tableName + '''
CREATE' + case is_unique when 1 then ' UNIQUE' else '' end + ' ' + c.indexType + ' INDEX
[' + indexName + ']
ON
[' + tableName + '] (' +
STUFF( ( select ', [' + a.columnName + ']' + case is_descending when 1 then ' DESC' else '' end
from CTE a where c.IndexId = a.IndexId and columnIncludes = 0 FOR XML PATH('')), 1 , 1, '')
+ ' ) ' +
ISNULL(CHAR(10) + ' INCLUDE (' + STUFF( ( select ', [' + b.columnName + ']' from CTE b where c.IndexId = b.IndexId and columnIncludes = 1 FOR XML PATH('')), 1 , 1, '') + ' ) ' + CHAR(10), CHAR(10)) +
'WITH (
DROP_EXISTING = ON,
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = ON,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [SECONDARY]
GO'
from CTE c
group by indexId, c.tableName, indexName, indexType, is_unique
order by c.tableName
/*
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MoveIndexToFileGroup]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[MoveIndexToFileGroup]
END
GO
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)
,key_ordinal TINYINT
)
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,'+ CASE WHEN @@VERSION LIKE '%Server 2005%' THEN '0 as Has_Filter, N'''' as Filter_Definition' ELSE 'si.Has_Filter,si.Filter_Definition' END +',sic.Key_Ordinal 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, sic.Key_Ordinal'
--PRINT @IndexSQL
-------------INSERT THE INDEX DATA INTO 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
ORDER BY key_ordinal ASC
--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
ORDER BY key_ordinal ASC
--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 (@ErrorMessage, 16, 1);
FINAL:
RETURN
END
GO
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment