CODEWITHSUNDEEP
sqlsql-server-2008indexingprimary-keyfilegroup
Pரதீப்
Pரதீப்

Reputation: 93694

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)??

Upvotes: 3

Views: 3732

Answers (2)

mehdi lotfi
mehdi lotfi

Reputation: 11571

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

Upvotes: 2

ceth
ceth

Reputation: 45295

  1. You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.

  2. If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.

For step by step instuctions you can use MS documentation.

Upvotes: 0

Related Questions