What does ON [PRIMARY] mean?

Question

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?

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

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

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

Answer 4

Please be aware about an important behavior related to 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 column which is primary key for the table. Metadata of the clustered index and data rows of a table can never be stored in two different file groups.

My database has two file groups:

1. PRIMARY
2. SECONDARY

Now take a look at the below script and execute it via SQL Server Management Studio(SSMS). Now something will happen against our expectation.

It will store the table's row data as well as the metadata pertaining to the clustered index both on the PRIMARY file group itself. This is happening even when I've mentioned a different file group ([SECONDARY]) for storing the table's row data. I wrote such an script deliberately to test the behvior:

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. Frankly speaking, I was expecting a run-time 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 different file groups 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 while the table's data rows reside 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 about how storing non-clustered indexes on a different file group can boost query performance.