Well, it can’t be done. You cannot move a table to a particular file in database.

What you can do is move the table to a particular file group. A FILEGROUP contains one or more data files. If the FILEGROUP contains more than one data file, SQL Server uses a proportional fill algorithm to make use of all data files in the FILEGROUP. On the other hand, if the FILEGROUP contains a single data file, then you can say that you have moved the table to a particular file without any doubts.

To move a table to a particular file file group, you need to re-create the clustered index on the table. Recreating an index effectively moves the table to the new FILEGROUP. The example below demonstrates the same.

Let’s create a test database to work with:

USE   [master]

GO

CREATE DATABASE [TestDB]

– Add a new FILEGROUP

ALTER DATABASE [TestDB] ADD FILEGROUP [SecondaryFG]

GO

– Add 2 data files to [SecondaryFG]

ALTER DATABASE [TestDB]

ADD FILE

(

      NAME = TestDB_2,

      FILENAME = 'C:\Database\Data\TestDB_2.ndf'

)

TO FILEGROUP [SecondaryFG]

GO

ALTER DATABASE [TestDB]

ADD FILE

(

      NAME = TestDB_3,

      FILENAME = 'C:\Database\Data\TestDB_3.ndf'

)

TO FILEGROUP [SecondaryFG]

GO

Here, we have created a database [TestDB] with two file groups, [PRIMARY] and [SecondaryFG]. Also [SecondaryFG] contains two data files TestDB_2 and TestDB_3. I have added two data files here because I also want to show how these files are filled together, i.e. when we will move a table to [SecondaryFG] both data files will receive 50% of the data since they are of same size.

USE   [TestDB]

GO

CREATE TABLE T1

(

      [ID]        INT    IDENTITY(1,1),

      [Name]      CHAR(8000),

      [Status]    SMALLINT   

)

GO

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T1_ID] ON [dbo].[T1]

(

      [ID] ASC

)

GO

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T1]

(

      [Status] ASC

)

GO

INSERT INTO T1 ([Name], [Status])

VALUES (REPLICATE('Test',2000), 1)

GO 1311

Currently both table and non clustered index reside on [PRIMARY] file group:

You can check the location of these objects by joining sys.indexes with sys.filegroups view:

SELECT      'Object Name' = OBJECT_NAME(IDX.object_id),

            'Index Name' = IDX.name,

            'FG Name' = FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

WHERE       IDX.object_id = OBJECT_ID('T1')

GO

Result Set:

Object Name   Index Name           FG Name

T1            IX_CL_T1_ID          PRIMARY

T1            IX_NCL_T2_Status     PRIMARY

(2 row(s) affected)

Now to move the table to [SecondaryFG] we need to drop and re-create the clustered index, we can do this in a single statement using the DROP_EXISTING clause as below:

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T1_ID] ON [dbo].[T1]

(

      [ID] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

Now the table has moved to [SecondaryFG], but wait, the non clustered index still exists on [PRIMARY] file group, this needs to be moved separately (all the non clustered indexes need to be dropped and re-created to move them to different file group):

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T1]

(

      [Status] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

Now you are done, table and all it’s indexes are now moved to [SecondaryFG], you can check this by using above query, which returns:

Object Name   Index Name           FG Name

T1            IX_CL_T1_ID          SecondaryFG

T1            IX_NCL_T2_Status     SecondaryFG

(2 row(s) affected)

If you check the physical file sizes for data files in [SecondaryFG] you will notice that both files have almost same size:

Also notice that the data file from [PRIMARY] file group is still same size, the free space is not returned to the file system. You can check the free space in file using below query:

SELECT      file_id, name, physical_name,

            'Free Space (MB)' =

            (size - FILEPROPERTY(name,'SpaceUsed'))/128.00

FROM        sys.database_files

Result Set:

file_id       name          physical_name                      Free Space (MB)

1             TestDB        C:\Database\Data\TestDB.mdf        10.937500

2             TestDB_log    C:\Database\Log\TestDB_log.LDF     0.492187

3             TestDB_2      C:\Database\Data\TestDB_2.ndf      7.562500

4             TestDB_3      C:\Database\Data\TestDB_3.ndf      6.937500

(4 row(s) affected)

To reclaim the disk space you can shrink the data file [TestDB] using DBCC SHRINKFILE, if that’s why you moved table to another file group.

Note: if your table contains LOB data, this method will not move LOB pages. To move LOB data you need to re-create the table. I will post about the same soon.

Hope This Helps!

Vishal (http://SqlAndMe.com)

Filed under: SQLServer