Create table on PRIMARY, question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create table on PRIMARY, question

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-01-28 : 19:41:23

I am trying to script out the creation of a table for the first time (usaly I do it in Management Studio) and looking at BOL the examples they show for creating a table all end with "ON PRIMARY;" but when I use it I am told "Incoorect syntax near PRIMARY, expecting ID, quotied_id, string, or text_lex. If I add [] around PRIMARY the error goes away. Can anyone tell me what I am doing wrong?

CREATE TABLE dbo.SectionInfo
(
	SectionID smallint IDENTITY(0,1) NOT NULL,
	SectionTitle varchar(250) NOT NULL,
	ShortName varchar(50) NOT NULL,
	LinkName varchar(50) NOT NULL,
	Synopsis varchar(MAX) NOT NULL
	CONSTRAINT PK_SectionID PRIMARY KEY CLUSTERED
)
ON PRIMARY;

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-28 : 21:12:59

PRIMARY near ON specifies file group to which table is to be created. The default file group is PRIMARY and all objects would be created in it unless you've created a separate file group and specified it in ON statement. thats why it generates the ON part when you script out.
it throws an error because PRIMARY is also a reserved word (see PRIMARY KEY above). once you escape it by [] it knows that its name of file group and runs fine.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-01-28 : 22:52:37

quote:
Originally posted by visakh16

PRIMARY near ON specifies file group to which table is to be created. The default file group is PRIMARY and all objects would be created in it unless you've created a separate file group and specified it in ON statement. thats why it generates the ON part when you script out.
it throws an error because PRIMARY is also a reserved word (see PRIMARY KEY above). once you escape it by [] it knows that its name of file group and runs fine.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I see so BOL just has a type-o then. Since primary is the default is there really a need to call it on each create script or is that just best practice?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-29 : 07:18:34

Primary is only the default filegroup by default. It's possible to change the default filegroup to something else.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:09:16

quote:
Originally posted by Eagle_f90

quote:
Originally posted by visakh16

PRIMARY near ON specifies file group to which table is to be created. The default file group is PRIMARY and all objects would be created in it unless you've created a separate file group and specified it in ON statement. thats why it generates the ON part when you script out.
it throws an error because PRIMARY is also a reserved word (see PRIMARY KEY above). once you escape it by [] it knows that its name of file group and runs fine.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I see so BOL just has a type-o then. Since primary is the default is there really a need to call it on each create script or is that just best practice?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

even if you didnt include On part, it will get created in PRIMARY by default. So you can ignore that if you need to create it in PRIMARY itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-29 : 14:31:52

However if you're working in a database that has the default filegroup set to something other than primary, you will need to include it if you want the table/index on primary.

--
Gail Shaw
SQL Server MVP

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-01-29 : 15:27:57

Yea, this is nothing fancy, just a normal setup. I am not good enough to changes these things or know why to

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources