sqlsql-server-2008indexingprimary-keyfilegroup

How to move existing Indexes and primary keys to secondary filegroup in SQL SERVER


All the indexes and primary keys were created in primary file(.mdf). Is there any way to move all the index and primary key to secondary filegroup(.ndf)??


Solution

  • Use Following query:

    DECLARE @SchemaName NVARCHAR(300),
            @ObjectName NVARCHAR(300),
            @IndexName NVARCHAR(300),
            @Columns NVARCHAR(MAX),
            @IncludeColumns NVARCHAR(MAX),
            @Command NVARCHAR(MAX)
    
    DECLARE IndexCursor CURSOR FOR
        SELECT SCHEMA_NAME(o.schema_id),OBJECT_NAME(o.object_id), i.name, 
                STUFF((SELECT ',['+c.name+']'
                FROM sys.index_columns ic 
                INNER JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_id
                WHERE ic.object_id = i.object_id
                    AND ic.index_id = i.index_id
                    AND ic.is_included_column=0 
                ORDER BY ic.key_ordinal
                FOR XML PATH('')
                ),1,1,'') IndexColumn,
                STUFF((SELECT ',['+c.name+']'
                FROM sys.index_columns ic 
                INNER JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_id
                WHERE ic.object_id = i.object_id
                    AND ic.index_id = i.index_id
                    AND ic.is_included_column=1
                ORDER BY ic.key_ordinal
                FOR XML PATH('')
                ),1,1,'') IncludeColumn,
                i.filter_definition
        FROM sys.indexes i
        INNER JOIN sys.objects o ON o.object_id = i.object_id
        INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
        WHERE ds.name = 'PRIMARY'
            AND OBJECTPROPERTY(i.object_id,'IsUserTable')=1
    
    OPEN IndexCursor
    FETCH NEXT FROM IndexCursor INTO @SchemaName, @ObjectName, @IndexName, @Columns, @IncludeColumns
    WHILE @@FETCH_STATUS=0 BEGIN
    
        SET @Command = 'ALTER INDEX ' + @IndexName + ' ON [' + @SchemaName + '].['+@ObjectName+']('+@Columns+') '+ ISNULL('Include ('+@IncludeColumns+')','') + ' ON [YourFileGroupName]'
        EXEC(@Command)
    
        FETCH NEXT FROM IndexCursor INTO @SchemaName, @ObjectName, @IndexName, @Columns, @IncludeColumns
    END
    CLOSE IndexCursor
    DEALLOCATE IndexCursor