Moving table or indexes on another filegroup - SQL Server by serverku

Because of data growth and performance issue we need to move tables or indexes to other file groups. The purpose of moving tables or indexes to improve the database performance as it reduces I/O from single drive or single filegroup. We can create another filegroup and move the tables and indexes to new created filegroups, then it will spit the I/O to two different filegroups. This way it will improve performance.

When we need to move the tables or indexes to another filegroup? We should move those table needs to move which are having more rows or heavily in size, Also need to move if those table's data are frequently updated. Here I would like to show the demo for the same. Let us see how can we move tables/indexes to another filegroup.

-- First we are creating database  
-- Main primary file on Primary filegroup 
-- Secondary files on secondary filegroup. 
-- Log files on different file group. 
CREATE DATABASE filegroupdb 
ON PRIMARY 
 ( name = filegroupdb_data, 
 filename = 'C:\FileGroupDB_Data.mdf', 
 size = 4 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ), 
filegroup secfilegroup 
 ( name = filegroupdb_data_1, 
 filename = 'C:\FileGroupDB_Data_1.ndf', 
 size = 2 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ) 
log ON 
 ( name = filegroupdb_log, 
 filename = 'C:\FileGroupDB_Log.ldf', 
 size = 2 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ) 

GO 

After creating database we will create a new table on Primary filegroup. If you not mentioned filegroup name, then it will be created on default filegroup. Let us create it.

USE filegroupdb 
GO 

-- Creating table on Primary filegroup 
IF( Object_id('tblFileGroup', 'u') > 0 ) 
  DROP TABLE tblfilegroup 
GO 

CREATE TABLE tblfilegroup 
  ( 
     objectid   INT NOT NULL PRIMARY KEY, 
     objectname VARCHAR(100), 
     createdate DATETIME 
  ) 
ON [PRIMARY] 

GO

The table is created and now we look the property of the table where it is created,

SP_HELP tblFileGroup

Now we are coming to the point which is tables/indexes actually will be moved. There are two ways through we can move the tables and indexes.

#1. By moving clustered index
Using this way we just need to move clustered index and tables and all indexes will be moved to different filegroup.

How can with #1 way?

-- Defining dropping constraint with move
ALTER TABLE tblFileGroup
DROP CONSTRAINT PK__tblFileG__9A619291029E5EB6 WITH (MOVE TO SecFileGroup)
GO

-- Adding constraint
ALTER TABLE tblFileGroup 
ADD CONSTRAINT PK__tblFileG__9A619291029E5EB6 PRIMARY KEY(ObjectId)
GO

Let us check again the properties of tables as successfully moved or not.

SP_HELP tblFileGroup

#2. Recreate a clustered index with drop existing
We just need to recreate a table's clustered index with drop existing clustered index on that.

How can with #2 way?

CREATE UNIQUE CLUSTERED INDEX PK__tblFileG__9A619291029E5EB6
ON tblFileGroup(ObjectId)
   WITH DROP_EXISTING
ON SecFileGroup

GO

You also worked with moving tables/indexes to another location, Share your ideas here!