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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] What does ON [PRIMARY] mean?

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to database-design