Moving all Heap Tables to another Filegroup in SQL Server

Question

In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this?

This question is only for Nonclustered Indexes.

https://stackoverflow.com/questions/4237238/moving-all-non-clustered-indexes-to-another-filegroup-in-sql-server

In the process of optimizing an older legacy database.

• I want to write a script to move all heap tables with a cursor loop similar to the Stack Overflow link above.

Answer 1

As far as I know, you have two options:

• Physically move the data. Create a new empty heap in the secondary filegroup / Copy the data / rename the object, everything inside of implicit transaction. And don't forget to script out indexes, constraints etc.
• Create a clustered index in the secondary filegroup, and then drop it.

I'd prefer the first option.

Answer 2

Below solutions can be helpful to achieve the goal:-

1. Create a clustered index on the table and mention to move it on secondary filegroup. see below link for reference. https://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database-filegroups/

2. Create another table with the same columns in the secondary filegroup. Copy data from one table to another table in chunks to optimize performance. This will copy data into secondary file allocation units. Then Drop the first table and rename the secondary table. There could be steps if the table is having a dependency.

Answer 3

I faced then same problem, move thousand of heaps to a different filegroup. The way i accomplished the task was CI index create and drop. If you are fine with this technique below is the script.

It enumerates heaps in a filegroup and generate the script for index create and drop. Run in the context of your database, possibile during maintenance window. Review the generated script before run. Work on MSSQM 2014 and above.

Hope it helps.

DECLARE @old_FG SYSNAME = 'PRIMARY'     -- where are the heaps
DECLARE @new_FG SYSNAME = 'USER_DATA'   -- filegroup we want heaps to go

DECLARE @fakeCI_sql NVARCHAR(MAX) = 'CREATE CLUSTERED INDEX {1} 
    ON {2}.{3}({4} ASC) 
    WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, DROP_EXISTING=OFF, ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) 
    ON {5};
    GO
    DROP INDEX {6} ON {7}.{8};
    GO'

DECLARE @t TABLE(id INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED, TableName SYSNAME, SchemaName SYSNAME, 
    IndexName SYSNAME NULL, IndexType NVARCHAR(128), ColumnName SYSNAME)

INSERT @t (TableName, SchemaName, IndexName, IndexType, ColumnName)
SELECT o.[name], sch.[name], i.[name], i.type_desc, col.ColumnName 
FROM sys.indexes i 
INNER JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas sch on sch.schema_id = o.schema_id
INNER JOIN (
    -- this join is just to retrieve the name of the fist column name of tables
    SELECT t.[name] AS TableName, s.[name] AS SchemaName, c.[name] AS ColumnName
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c on c.object_id = t.object_id
    WHERE 
        c.column_id=1           -- first column
        and t.is_ms_shipped = 0
) col ON  col.TableName=o.[name] AND col.SchemaName=sch.[name]
WHERE i.data_space_id = fg.data_space_id 
AND o.type = 'U'            -- user table
and o.is_ms_shipped=0   
AND i.type_desc IN ('HEAP') -- only heap table
AND fg.[name] = @old_FG     -- in this filegroup



DECLARE @id INT 
DECLARE @TableName SYSNAME, @SchemaName SYSNAME, @ColumnName SYSNAME
DECLARE @IndexName NVARCHAR(128), @IndexType NVARCHAR(128), @outp NVARCHAR(MAX)

WHILE (1=1)
BEGIN 
    SELECT TOP 1 @id=id, @tablename=TableName, @SchemaName=SchemaName, @ColumnName=ColumnName,
        @IndexName = CONCAT('_CI_', @TableName,'_', @ColumnName), @IndexType=IndexType FROM @t
    
    IF @@ROWCOUNT=0
        BREAK;

    SET @outp=@fakeCI_sql
    SET @outp = replace(@outp,'{1}', QUOTENAME(@IndexName))
    SET @outp = replace(@outp,'{2}', QUOTENAME(@SchemaName))
    SET @outp = replace(@outp,'{3}', QUOTENAME(@TableName))
    SET @outp = replace(@outp,'{4}', QUOTENAME(@ColumnName))
    SET @outp = replace(@outp,'{5}', QUOTENAME(@new_FG))
    SET @outp = replace(@outp,'{6}', QUOTENAME(@IndexName))
    SET @outp = replace(@outp,'{7}', QUOTENAME(@SchemaName))
    SET @outp = replace(@outp,'{8}', QUOTENAME(@TableName))

    print @outp -- script out 

    DELETE FROM @t WHERE id=@id
END