SQL Server – Primary Key on Different Filegroup Impact

ddlfilegroupssql servert-sql

Creating a table on a different filegroup is absolutely no problem when using Sql Server.

create table common.test
(
    id int,

    CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON [common_index],
) ON [common_data]

But what happens to the data in the background? Are they really stored on two different filegroups? Because when you look at the table properties in Management Studio it show common_index as storage.

If this is true, why does it even work to define them on two seperate filegroups?

What would happen with the stored data when the primary key is dropped and recreated on another filegroup?

Best Answer

It's all about clustered vs non-clustered indexes actually. Primary key has nothing to do with it. A clustered index IS the data. The actual table data is contained in the data pages of the clustered index.

If you put a clustered index on a table then the data is stored where you tell the clustered index to be. If you changed your code above to be a non-clustered primary key then the data would be in [common_data] (and it would be a heap as it happens) and the non-clustered index for the primary key would be in [common_index]. If you then created a clustered index and pointed it to [uncommon_data] then the table data would be moved to the new filegroup and nothing would remain on [common_data].

Oh, and the reason you can do specify two separate filegroups is because you are working with two different parts of the syntax. The ON [common_data] (FG for the table) tells SQL where to put the table. The ON [common_index] (the FG for the index) tells SQL where to put the index. In this case because it's a clustered index it forces the data to go with it.

Related Question