How to move Non-Clustered indexes from one Filegroup to another Filegroup within a database

PandianS
Posted by PandianS under Sql Server category on | Points: 40 | Views : 5966
Normally, Maintening the Indexes on different filegroup is better performance. But there is some limitations..
1. Unique Index
2. Clustered Index
3. Primary Key with Clustered or Non-Clustered Index can not / may not be moved to another filegroup.

Moving Non-Clustered Indexes from PRIMARY filegroup to Secondary Filegroup.

Steps to be followed:
1. Create a new filegroup named : Secondary
2. Add data files to the Filegroup.
3. Execute the script given below
DECLARE @FGFrom	VARCHAR(50), 
@FGTo VARCHAR(50)

SELECT @FGFrom = 'PRIMARY',
@FGTo = 'Secondary'

BEGIN TRY
BEGIN TRAN
CREATE TABLE #NonClusteredIndex
(
Id INT IDENTITY(1,1),
NCI VARCHAR(MAX));

DECLARE @intIndex INT,
@intIndexCount INT,
@NCIScript VARCHAR(MAX)

SELECT @intIndex = 1

INSERT #NonClusteredIndex(NCI)
SELECT 'CREATE NONCLUSTERED INDEX ' + I.name + ' ON ' + OBJECT_NAME(i.[object_id]) + '(' + index_col(OBJECT_NAME(i.[object_id]),I.index_id,1) + ') WITH (DROP_EXISTING = ON,ONLINE = ON) ON ' + @FGTo
FROM sys.indexes i JOIN sys.data_spaces s
ON(i.data_space_id = s.data_space_id)
JOIN sys.objects o ON(o.[object_id] = i.[object_id])
WHERE o.[type] ='U'
AND i.type_desc ='NONCLUSTERED'
AND I.is_unique = 0
AND S.name = @FGFrom

SELECT @intIndexCount = @@ROWCOUNT

WHILE(@intIndex <= @intIndexCount)
BEGIN
SELECT @NCIScript = NCI FROM #NonClusteredIndex WHERE ID = @intIndex
EXEC(@NCIScript)
SELECT @intIndex = @intIndex + 1
END

DROP TABLE #NonClusteredIndex
COMMIT
END TRY
BEGIN CATCH
DECLARE @ErrMessage NVARCHAR(MAX)
SELECT @ErrMessage = ERROR_MESSAGE()
RAISERROR(@ErrMessage,16,1)
ROLLBACK
END CATCH

You can changes the script as yours...

Comments or Responses

Login to post response