How to create a non-clustered index in Create Table?

Question

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 ) 

The result is Incorrect syntax near the keyword 'nonclustered' . I referenced the MSDN help for the create table syntax. I'm not sure what is wrong here.

Answer 1

The help in online books really mentions the CLUSTERED keyword, but this only applies to UNIQUE or PRIMARY KEY restrictions. Both of these restrictions create an index, and you can specify whether this index should be clustered or non-clustered.

You cannot use this 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 ) 

Answer 2

Delete this non-clustered keyword and use the CREATE INDEX statement to add an index to this table, the documentation for it can be read in:

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

The 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" } ] [ ; ] 

The code is here:

 CREATE NONCLUSTERED INDEX index_clustered ON FavoriteDish(CelebrityName asc)