Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to move table to particular file in database

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:

image

 

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:

image

 

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

Comments

Posted by Jai-SQL DBA on 25 April 2014

Can you explain how moving\Creating index took all the data to secondary Filegroup.

I have a table for which my index was created on secondary file group but the table is still there on primary .

Also whats the way to move only index on secondary filegroup ??..can you please answer the above queries.

Leave a Comment

Please register or log in to leave a comment.