Change SQL Server HEAP table file group

Change SQL Server HEAP table file group

                         I have a small worker-round for sql server database, HEAP tables move to different file group. Of-cause this is bit critical work before starts I have a two solutions,

• Create CLUSTERED index of all given HEAP tables.
• Move all HEAP table to new file group.   

And every one is know, if you recreate or newly create CLUSTERED index on SQL server table, then index will be created given file group.which means simply move data to newly given file group.

But what will happens if we drop again,

• Will HEAP table again move to old file group?
• Or HEAP table remain as same file group?

 what Microsoft Saying?

Managing Heaps

• To create a heap, create a table without a clustered index. If a table already has a clustered index, drop the clustered index to return the table to a heap.

• To remove a heap, create a clustered index on the heap.

• To rebuild a heap to reclaim wasted space, create a clustered index on the heap, and then drop that clustered index. 

Will Have test this,

USE [MSDN]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[DatabaseUserGroup];
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[DatabaseUserGroup](
[colid] [int] IDENTITY(1,1) NOT NULL,
[colname] [varchar](100) NULL
) ON [PRIMARY];
END
GO
INSERT INTO [dbo].[DatabaseUserGroup] ([colname]) SELECT name FROM master.sys.objects;
GO 10
SELECT count(*) cnt FROM [dbo].[DatabaseUserGroup];
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND i.type_desc='HEAP'
AND o.[name]='DatabaseUserGroup';
GO
CREATE UNIQUE CLUSTERED INDEX PK_CIX_DatabaseUserGroup_colid ON [dbo].[DatabaseUserGroup](colid) WITH (ONLINE=ON) ON [MAIN];
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND o.[name]='DatabaseUserGroup';
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND name = N'PK_CIX_DatabaseUserGroup_colid') BEGIN
DROP INDEX [PK_CIX_DatabaseUserGroup_colid] ON [dbo].[DatabaseUserGroup] WITH ( ONLINE = ON );
END
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND o.[name]='DatabaseUserGroup';

Well.. Well... table remain as newly given file group.When a clustered index is created, SQL Server will temporarily duplicate the data from the heap into the clustered index key order and remove the original pages associated with the heap.But remember this is bit costly operation(depend on how large your table) you need to make sure use this very less server busy time.According to Microsoft,

Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.    

Cheers...
and Most welcome your comments and ideas...