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 :
Secondary2. 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...