此SQL创建表语句有什么问题？ [英] What's wrong with this SQL Create Table Statement?

问题描述

此SQL查询是由SQL Server Managment Studio生成的，它向我抛出错误：

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

错误是：消息170，级别15，状态1，第27行第27行：'（'。附近的语法不正确。指向 [CellPhone_Prefix] [int] NULL，，但这行对我来说很好。

可能出什么问题了？

编辑：

我刚刚注释掉了

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?

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]

现在可以正常工作了，为什么？

编辑2：

我将其缩小为：

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]  

我错过了一些东西

推荐答案

我认为这取决于SQL Server数据库版本。

I think this is down to SQL server database version.

我尝试对SQL Server 2000数据库使用SSMS 2005进行查询，但由于描述的错误而失败。

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

当我连接到SQL Server 2005服务器时该查询执行完美。

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

您是否确实检查过服务器版本，而不仅仅是SSMS版本。

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

根据 SQL Server 2000 语法， WITH部分仅允许FillFactor被设置t，没有其​​他内容：

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 } ]
] 

这与 SQL Server 2008 / 2005 语法，该语法允许在方括号内使用多个选项：

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" } ] 
    .
    .
    .
}

这篇关于此SQL创建表语句有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！