Moving Large Table to Different File Group

--Moving Large Table to Different File Group

--Step 1. Find the space occupied by tabe which needs to be moved to file group.

sp_spaceused 'ACCident' -- This will list table name with rows, reserved data, index size and unused space

--Step 2.  Create a new filegroup by altering database.

ALTER Database Databasename add filegroup [SecondaryData] -- This will add a new filegroup

--Step 3. Create a file and point to this new filegroup.

ALTER DATABASE Databasename
ADD FILE
( NAME = XFILENAME,
FILENAME = 'new path\SALES.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP [SECONDERYDATA]
GO

--Step 4. Alter the table to change index on the table

ALTER TABLE [BIGTABLE]
DROP CONSTRAINT [BIGTABLE_PK] WITH (MOVE TO SECONDERYDATA)

--Step 5. Recreate the index

ALTER TABLE [BIGTABLE]
ADD CONSTRAINT [BIGTABLEE_PK] PRIMARY KEY CLUSTERED
( [column name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [SECONDERYDATA]