Create a table on a filegroup other than the default

Question

The title is clear enough, I created a new Filegroup "ArchiveFileGroup":

ALTER DATABASE MyDataBase
ADD FILEGROUP ArchiveFileGroup;
GO

I want to create a table called : arc_myTable in order to store old data from this one : myTable

I used the following query :

CREATE TABLE [dbo].acr_myTable(
    [Id] [bigint] NOT NULL,
    [label] [nvarchar](max) NOT NULL,
)on ArchiveFileGroup 

I'm not sure if it's the right way, I don't know where the FileGroup is created to check if it contains the table.

Answer 1

You can easily check with this sql query:

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
GO

Just add:

AND f.name = ArchiveFileGroup

to see everything in your new filegroup or:

AND o.name = acr_myTable

to see where your table is located.

If you never added a file to your filegroup, then I would expect an error but you didn't include either an error message or anything saying you did create a file. If you did not, I suggest starting at the microsoft documentation if needed.

The OP found the this helpful trying to create a new file in his filegroup.

Answer 2

You can use sys.filegroups to see all the created file groups in your server like

SELECT *
FROM sys.filegroups

See here for more information List All Objects Created on All Filegroups