MS SQL: syntax error in create table sql

[Plater]

Using Management Studio (for MS SQL server 8) I had created a table, given a primary key and created a unique key.
I then had the management studio "script 'create' to file" and it saved an SQL statement (below).
I then dropped/deleted the table and tried to execute the creation statement.
It says "Incorrect syntax near '('. " and references the block connected with the "WITH" statement. (Actually both of them cause the error and removing them gets rid of it.
Is there a way to keep the information contained in the WITH statement in my table creation SQL? Is it even required info?

[code=sql]
USE [MyProducts]
GO
/****** Object: Table [dbo].[Category] Script Date: 10/12/2007 11:22:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Category]
(
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[CategoryNumber] [int] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORE COMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCK S = ON
) ON [PRIMARY],
CONSTRAINT [IX_UniqueCatego ryNumber] UNIQUE NONCLUSTERED
(
[CategoryNumber] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORE COMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCK S = ON
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
[/code]

[iburyak]

Usually you never use ‘With’ statement while creating a table.

Remove this statement and create table without it then go to table definition and see it should be picked by default.


Good Luck.

[Plater]

it "appeared" to create correctly without the WITH statements.
I was just bothered by the management studio auto-createing the statement then telling my there were syntax errors. The msdn help on the T-SQL for it shows WITH statements in the smae style.
All well, I'll just remove them.

Thanks.

[iburyak]

You can change some settings but not all.
Most of them are server level settings and can not be changed and just read only.

For example you see ON [PRIMARY] and it allows you to put data on one device and indexes on different devices or even spread tables among several devices but server should see your other devices first before you are trying to create something on them. So it is available but for people who know what they doing…
You are doing grate so good luck.