0

I have three filegroups in my database:

enter image description here

Default is TERTIARY. Now I want to create table on PRIMARY and it shows the following error

Incorrect syntax near the keyword 'primary'.

create table tb1
(
    id int identity primary key nonclustered,
    somecolumn nvarchar(15)
)
on PRIMARY;

When I change the default filegroup option to PRIMARY it lets to create tables on PRIMARY. Why does it show this error when my syntax is not wrong?

Thanks for your time.

closed as off-topic by Erik Darling, hot2use, Philᵀᴹ, Colin 't Hart, Md Haidar Ali Khan Aug 5 '18 at 3:50

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, hot2use, Philᵀᴹ, Colin 't Hart, Md Haidar Ali Khan
If this question can be reworded to fit the rules in the help center, please edit the question.

5

I think your problem stems from the fact that PRIMARY is a reserved word. There are several options for the ON clause of a CREATE TABLE statement and putting square brackets around the word [PRIMARY] eliminates confusion about what you are actually asking SQL Server to do.

Not the answer you're looking for? Browse other questions tagged or ask your own question.