CODEWITHSUNDEEP
sqlsql-servercreate-table
the_drow
the_drow

Reputation: 19181

What's wrong with this SQL Create Table Statement?

This SQL query is generated by SQL Server Managment Studio and it throws me an error: 

USE [database_name]
GO
/****** Object:  Table [dbo].[UserAddress]    Script Date: 02/17/2010 11:21:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UserAddress]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NULL,
    [AddressName] [nvarchar](25) NULL,
    [Fname] [nvarchar](15) NULL,
    [LName] [nvarchar](20) NULL,
    [City] [nvarchar](15) NULL,
    [Street] [nvarchar](30) NULL,
    [StreetNum] [nvarchar](5) NULL,
    [FloorNum] [int] NULL,
    [AptNum] [int] NULL,
    [ZipCode] [int] NULL,
    [Phone] [varchar](15) NULL,
    [Phone_Prefix] [int] NULL,
    [CellPhone] [varchar](15) NULL,
    [CellPhone_Prefix] [int] NULL,
    [Fax] [varchar](15) NULL,
    [Fax_Prefix] [int] NULL,
    [Primary] [bit] NULL,
    CONSTRAINT [PK_UserAddress] PRIMARY KEY CLUSTERED 
    (
        [ID] 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
SET ANSI_PADDING OFF

The error is: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near '('. pointing at [CellPhone_Prefix] [int] NULL, but this line looks fine to me.
What could be wrong?
EDIT:
I just commented out the

 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [ClientStoreID] ASC,
    [Uname] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

and now it works, why?
EDIT 2:
I narrowed it down to: 

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Am I missing something here?

Upvotes: 3

Views: 9754

Answers (1)

Simon P Stevens
Simon P Stevens

Reputation: 27499

I think this is down to SQL server database version.

I tried your query using SSMS 2005 against a SQL server 2000 database and it fails with the same error you describes.

When I connected to a SQL server 2005 server the query executes perfectly.

Have you definitely checked your server version, and not just your SSMS version.

According to the SQL Server 2000 syntax, the WITH part only allows FillFactor to be set, and nothing else:

< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
    [ CLUSTERED | NONCLUSTERED ]
    { ( column [ ASC | DESC ] [ ,...n ] ) }
    [ WITH FILLFACTOR = fillfactor ]
    [ ON { filegroup | DEFAULT } ]
]

This differs from the SQL Server 2008/2005 syntax which allows multiple options within brackets:

< table_constraint > ::= [ CONSTRAINT constraint_name ] 
{  { PRIMARY KEY | UNIQUE } 
    [ CLUSTERED | NONCLUSTERED ] 
    (column [ ASC | DESC ] [ ,...n ] ) 
    [ WITH FILLFACTOR = fillfactor | WITH ( <index_option> [ , ...n ] ) ]
    [ ON { partition_scheme_name (partition_column_name) | filegroup | "default" } ] 
    .
    .
    .
}

Upvotes: 9

Related Questions