What does ON [PRIMARY] mean?
Solution 1
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.
See MSDN for the full syntax.
Solution 2
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.
Solution 3
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.
Solution 4
I intend to complement Mark S.'s ansnwer to add an important note regarding file groups.
Using OP's SQL Script you can never mention two different file groups i.e. one for storing your data rows and the other for index data structure. This is not allowed.
This is due to the fact that the index being created in this case is a clustered Index on the primary key column. Metadata of the clustered index and the data rows of the table can NEVER be two different file groups.
If 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.
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
More interestingly, the above script runs to completion without any error(I was expecting an error as I had given two different file groups). SQL Server does the trick behind the scene silently without throwing any error.
NOTE: But yes, the index can reside on a different file group in case of non-clustered indexes.
SQL Script shown below creates a non-clustered index. The non-clustered index will get created on [SECONDARY]
file group instead while the table data resides on [PRIMARY]
file group:
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 here on how storing non-clustered indexes on a different file group can boost query performance
Related videos on Youtube
Icono123
I spend most of my time developing in C#. For the past two years I've been developing dashboards within SharePoint. I hold four MCTS certifications and work for a government consulting firm in the DC area.
Updated on March 04, 2022Comments
-
Icono123 about 2 years
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:
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?
-
MGOwen about 13 yearsThis also means it's usually useless and can be safely removed from the script.
-
Mark Sowul over 10 yearsYes, in the same way you can just omit variable initializations to 0 and false, because it's just the default, right?
-
jpmc26 about 9 years@MarkSowul Unless you have a good reason to use this to optimize performance, yes, it's fine to omit it and let the default happen. (Hence the "usually" MGOwen included.) Initializing variables to
0
orfalse
is about ensuring that your code is operating in a known state, which is a logical and correctness concern and not an optimization concern. -
RBT almost 8 yearsMSDN link seems to be taking to a download page instead.
-
RBT almost 8 yearsI see the
ON PRIMARY
syntax twice in the script - One for the table and another for the table constraint. What does it signify in case of the table constraint in terms of storage? It sounds irrelevant or redundant to me. Syntactically, it should have been sufficient to mention it once at the table level or is it really possible to store the table on PRIMARY file group and table constraint data on NON-PRIMARY file group? -
RBT almost 8 yearsWill it have a negative performance impact if I store the table on PRIMARY file group and table constraint or index data structure on a different file group?
-
codingbadger almost 8 years@RBT There are lots of variables that can affect this, and usually lots of answers will begin with "It depends but..." see dba.stackexchange.com/questions/2626/… and related questions
-
shekhar about 7 yearsHere is the actual MSDN link. The one in the answer no longer works and I cannot edit the post !