SQL 在默认文件组以外的文件组上创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24087614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a table on a filegroup other than the default
提问by GSDa
The title is clear enough, I created a new Filegroup "ArchiveFileGroup":
标题够清楚了，我新建了一个文件组“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
我想创建一个名为：arc_myTable 的表，以便存储来自这个表的旧数据：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.
我不确定这是否正确，我不知道在哪里创建 FileGroup 来检查它是否包含表。
回答by Vulcronos
You can easily check with this sql query:
您可以使用此sql 查询轻松检查：
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 documentationif needed.
如果您从未将文件添加到文件组中，那么我预计会出现错误，但您没有包含错误消息或任何说明您确实创建了文件的内容。如果没有，我建议在需要时从microsoft 文档开始。
The OP found the thishelpful trying to create a new file in his filegroup.
OP 发现这有助于在他的文件组中创建新文件。
回答by Rahul
You can use sys.filegroupsto see all the created file groups in your server like
您可以使用sys.filegroups查看服务器中所有创建的文件组，例如
SELECT *
FROM sys.filegroups
See here for more information List All Objects Created on All Filegroups
有关详细信息，请参阅此处列出在所有文件组上创建的所有对象
回答by Venkataraman R
It is old post. Want to add information, it might help somebody in future.
这是旧帖子。想要添加信息，它可能会在未来帮助某人。
sp_help <table_name>
sp_help <table_name>
You can see the filegroup, on which the table is created.
您可以看到在其上创建表的文件组。
回答by user11256637
NB. You can check by Right-clicking on the table then select properties. on the storage you can see to which the filegroup the new table is belonging
注意。您可以通过右键单击表格然后选择属性来检查。在存储上，您可以看到新表所属的文件组
In your case: It will create the table on the default filegroup, not on the new filegroup you created. A filegroup is logical and used to create a secondary file. ex. if you need to create the table on a different location than the default drive, you have to define fileName for the new filegroup.
在您的情况下：它将在默认文件组上创建表，而不是在您创建的新文件组上。文件组是合乎逻辑的，用于创建辅助文件。前任。如果您需要在与默认驱动器不同的位置创建表，则必须为新文件组定义 fileName。
ALTER DATABASE [db] ADD FILEGROUP [NewFileGroup]
ALTER DATABASE [db] ADD FILE ( NAME = N'NewFile', FILENAME = N':D\..\Newfile.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [NewFileGroup]
GO
走
