Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted April 3, 2017

How to Move a Table to a Different File Group

By Greg Larsen

There may be a time when you want to move a table from one file group to another.  There could be lots of different reasons for wanting to move your data file.  One of the reasons why you might want to do this is to improve performance.  It is easy to move a table from one filegroup to another if it contains a clustered index. 

To show you how to move a table from one file group to another let me create an example.

First let me create a sample database using the following code:

-- Create Sample database
USE master;
GO
CREATE DATABASE MoveTable 
ON PRIMARY (NAME = MoveTable1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MoveTable1.mdf'),
FILEGROUP MoveFile2
(NAME = MoveFile2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MoveTable2.ndf') ;
GO

With this code I have created a database named “MoveTable” that contains two filegroups: PRIMARY and MoveFile2. 

Now let me create a sample table that resides on the PRIMARY file group using the following code:

-- create table ToMove on Primary filegroup
USE MoveTable;
GO
CREATE TABLE dbo.ToMove
(ID int,
SampleData varchar(10)) ON [PRIMARY]
CREATE CLUSTERED INDEX IX_ID ON MoveTable.dbo.ToMove(ID);
GO 

Here I have created a table named “ToMove” on the PRIMARY filegroup and then created a clustered index on the table named “IX_ID”.  To verify this table is on the PRIMARY filegroup I will run the following code:

-- Determine which filegroup table ToMove resides on
SELECT o.[name] AS TableName, i.[name] AS IndexName, f.[name] as FileGroup 
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 i.data_space_id = f.data_space_id
AND o.[name] = 'ToMove'

When I run this code I get following output:

TableName                 IndexName                  FileGroup
------------------------- -------------------------- ----------------------------
ToMove                      IX_ID                          PRIMARY
 

As you can see by the output above my sample table “ToMove” resides on the PRIMARY filegroup and has a clustered index name of IX_ID.

To move this table to the second file group name “MoveFile2” all I have to do is run the following command:

-- Move table to filegroup MoveTable2
CREATE CLUSTERED INDEX IX_ID ON MoveTable.dbo.ToMove(ID)
WITH(DROP_EXISTING=ON,Online=ON) ON [MoveFile2]
GO

This command moves the table from the PRIMARY filegroup to the MoveFile2 filegroup by dropping and recreating the clustered index.  It does this operation while the index is online.  You can verify that the table ToMove got moved by running the code above that displays the filegroup in which this table resides.

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM