Assign table to filegroup

Joe Chan 16 Feb, 2019

MSSQL-Feature-Image

We can assign table to filegroup when table creation.
Besides, we can move table to another filegroup.

How to assign table to filegroup via GUI:
1. Expand “Databases” > Expand suitable database > Right-Click “Tables”
2. Select “New” > Select “Table…”
3. Expand “Regular Data Space Specification” in right panel
4. Choice suitable filegroup in “Filegroup or Partition Scheme Name”

How to assign table to filegroup via T-SQL:

-- Create table named "TEST_Table_02" and assign to filegroup named "Test_01"
CREATE TABLE dbo.TEST_Table_02
(
ID_ int not null IDENTITY(1,1) PRIMARY KEY,
Value nvarchar(80) NULL
) on Test_01

How to move table to another filegroup:
When we recreate clustered index to a different filegroup, the data of table will be move to this filegroup.

--Create Table with two index at filegroup "PRIMARY"
CREATE TABLE dbo.TEST_Table_02
(
ID_ int not null IDENTITY(1,1),
Value nvarchar(80) NULL,
INDEX IX_VALUE NONCLUSTERED (Value),
CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID_)
)
-- Move CLUSTERED INDEX "PK_ID" to filegroup "TEST_02"
CREATE UNIQUE CLUSTERED INDEX [PK_ID]
ON [dbo].[TEST_Table_02]([ID_])
WITH DROP_EXISTING
ON TEST_02
-- Move NONCLUSTERED INDEX "IX_VALUE" to filegroup "TEST_02"
CREATE NONCLUSTERED INDEX [IX_VALUE]
ON [dbo].[TEST_Table_02]([Value])
WITH DROP_EXISTING
ON TEST_02

Author: Joe Chan