What does ON [PRIMARY] mean?

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?

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:

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.

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.

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:

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. Here is one such link.