如何在Create Table中创建非聚簇索引？

Create table FavoriteDish    
(    
FavID int identity (1,1) primary key not null,    
DishID int references Dishes(DishID) not null ,    
CelebrityName nvarchar(100)  nonclustered not null     
)

This results in

这导致了

Incorrect syntax near the keyword 'nonclustered'.

关键字'nonclustered'附近的语法不正确。

I referred to the MSDN help for the create table syntax. I am not sure whats wrong here.

我参考了创建表语法的MSDN帮助。我不确定这里有什么不对。

2 个解决方案

#1

The help in books online does in fact mention the keyword CLUSTERED, but it is only relevant for UNIQUE or PRIMARY KEY constraints. Both these constraints create an index, and you can specify if that index is to be clustered or non-clustered.

实际上，在线图书中的帮助确实提到了关键字CLUSTERED，但它仅与UNIQUE或PRIMARY KEY约束相关。这两个约束都会创建索引，您可以指定该索引是集群还是非集群。

You cannot use that syntax to create a standard non clustered index.

您无法使用该语法来创建标准的非聚集索引。

Create table FavoriteDish    
(    
FavID int identity (1,1) primary key not null,    
DishID int references Dishes(DishID) not null ,    
CelebrityName nvarchar(100)   constraint ux_CelebrityName unique NONCLUSTERED not null     
)

#2

Erase this nonclustered keyword and use CREATE INDEX statement to add index to this table, documentation of this can read in:

擦除此非聚集关键字并使用CREATE INDEX语句向此表添加索引，此文档可以读入：

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Syntax is here:

语法在这里：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

Code is here:

代码在这里：

CREATE NONCLUSTERED INDEX index_clustered ON FavoriteDish(CelebrityName asc)

#1