USE master
GO
ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_1
GO
ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_2
GO
ALTER DATABASE TEST
ADD FILE
( NAME = TEST1,
FILENAME = 'D:\SQL\Data\TEST_1.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB
)
TO FILEGROUP TEST_DATA_1
GO
ALTER DATABASE TEST
ADD FILE
( NAME = TEST2,
FILENAME = 'D:\SQL\Data\TEST_2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB
)
TO FILEGROUP Test_DATA_2
GO
USE TEST
GO
CREATE TABLE TAB1
(
TAB1_ID INT IDENTITY(1,1),
TAB1_NAME VARCHAR(100),
CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
) ON TEST_DATA_1 -- Filegroup we created.
GO
INSERT INTO TAB1(TAB1_NAME)
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO
exec sp_help TAB1
/*
Selected output of above command is
Data_located_on_filegroup
—————————
TEST_DATA_1
index_name index_description
———- ——————————————————-
PK_TAB1 clustered, unique, primary key located on TEST_DATA_1
constraint_type constraint_name
————————– —————-
PRIMARY KEY (clustered) PK_TAB1
Above result indicates that PK_TAB1 constraint is used to create the clustered index on the table TAB1.
Please note that whenever a primary key constraint is defined, and clustered index does not exist on the
table, SQL Server will create clustered index for the primary key constraint. It is not the same for
UNIQUE constraint. Unique constraint/ index will always be non-clustered unless other wise it is specified
during creation of constraint or index. We had covered this before in the differences between a primary
key and a unique constraint blog post.
In order to move the table to a different file group, we need to use the drop constraint command along
with the MOVE TO option as shown below. Once the table is moved to a new file group, we can re-create
the primary key constraint.
*/
ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2)
GO
exec sp_help TAB1
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO
exec sp_help TAB1
/*
After executing the above command, table TAB1 will now reside on filegroup TEST_DATA_2. If you re-execute
sp_help stored procedure, it will show the filegroup change for table TAB1.
*/
?l'ta?m