SQL ON [PRIMARY] 是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2798213/
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
What does ON [PRIMARY] mean?
提问by Icono123
I'm creating an SQL setup script and I'm using someone else's script as an example. Here's an example of the script:
我正在创建一个 SQL 安装脚本，并以其他人的脚本为例。这是脚本的示例：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Categories](
[CategoryID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_be_Categories_CategoryID] DEFAULT (newid()),
[CategoryName] [nvarchar](50) NULL,
[Description] [nvarchar](200) NULL,
[ParentID] [uniqueidentifier] NULL,
CONSTRAINT [PK_be_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Does anyone know what the ON [PRIMARY] command does?
有谁知道 ON [PRIMARY] 命令的作用？
回答by blowdart
When you create a database in Microsoft SQL Server you can have multiple file groups, where storage is created in multiple places, directories or disks. Each file group can be named. The PRIMARY file group is the default one, which is always created, and so the SQL you've given creates your table ON the PRIMARY file group.
在 Microsoft SQL Server 中创建数据库时，您可以拥有多个文件组，其中在多个位置、目录或磁盘中创建存储。每个文件组都可以命名。PRIMARY 文件组是默认的，它总是被创建，因此您提供的 SQL 在 PRIMARY 文件组上创建您的表。
See MSDNfor the full syntax.
有关完整语法，请参阅MSDN。
回答by codingbadger
It refers to which filegroup the object you are creating resides on. So your Primary filegroup could reside on drive D:\ of your server. you could then create another filegroup called Indexes. This filegroup could reside on drive E:\ of your server.
它指的是您正在创建的对象所在的文件组。因此，您的主文件组可以驻留在服务器的驱动器 D:\ 上。然后，您可以创建另一个名为 Indexes 的文件组。该文件组可以驻留在服务器的驱动器 E:\ 上。
回答by Mark S.
ON [PRIMARY] will create the structures on the "Primary" filegroup. In this case the primary key index and the table will be placed on the "Primary" filegroup within the database.
ON [PRIMARY] 将在“主要”文件组上创建结构。在这种情况下，主键索引和表将放置在数据库内的“主”文件组中。
回答by RBT
To add a very important note on what Mark S. has mentioned in his post. In the specific SQL Script that has been mentioned in the question you can NEVER mention two different file groups for storing your data rows and the index data structure.
对 Mark S. 在他的帖子中提到的内容添加一个非常重要的注释。在问题中提到的特定 SQL 脚本中，您永远不能提到用于存储数据行和索引数据结构的两个不同的文件组。
The reason why is due to the fact that the index being created in this case is a clustered Index on your primary key column. The clustered index data and the data rows of your table can NEVER be on different file groups.
原因是因为在这种情况下创建的索引是主键列上的聚集索引。聚集索引数据和表的数据行永远不能位于不同的文件组中。
So in case you have two file groups on your database e.g. PRIMARY and SECONDARY then below mentioned script will store your row data and clustered index data both on PRIMARY file group itself even though I've mentioned a different file group ([SECONDARY]) for the table data. More interestingly the script runs successfully as well (when I was expecting it to give an error as I had given two different file groups :P). SQL Server does the trick behind the scene silently and smartly.
因此，如果您的数据库中有两个文件组，例如 PRIMARY 和 SECONDARY，那么下面提到的脚本会将您的行数据和聚集索引数据都存储在 PRIMARY 文件组本身上，即使我已经提到了[SECONDARY]表数据的不同文件组 ( ) . 更有趣的是，脚本也成功运行（当我期望它给出错误时，因为我给出了两个不同的文件组：P）。SQL Server 在幕后默默而巧妙地完成了这项工作。
CREATE TABLE [dbo].[be_Categories](
[CategoryID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_be_Categories_CategoryID] DEFAULT (newid()),
[CategoryName] [nvarchar](50) NULL,
[Description] [nvarchar](200) NULL,
[ParentID] [uniqueidentifier] NULL,
CONSTRAINT [PK_be_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [SECONDARY]
GO
NOTE: Your index can reside on a different file group ONLY if the index being created is non-clustered in nature.
注意：您的索引只能驻留在不同的文件组中，前提是正在创建的索引本质上是非集群的。
The below script which creates a non-clustered index will get created on [SECONDARY]file group instead when the table data already resides on [PRIMARY]file group:
[SECONDARY]当表数据已经驻留在[PRIMARY]文件组上时，创建非聚集索引的以下脚本将在文件组上创建：
CREATE NONCLUSTERED INDEX [IX_Categories] ON [dbo].[be_Categories]
(
[CategoryName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Secondary]
GO
You can get more information on how storing non-clustered indexes on a different file group can help your queries perform better. Hereis one such link.
您可以获得有关在不同文件组上存储非聚集索引如何帮助您更好地执行查询的更多信息。这是一个这样的链接。
