sqlsql-serverdatabasefilegroup

Create a table on a filegroup other than the default

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.

Solution

  • 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.