How to create nonclustered 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     
)

This results in

Incorrect syntax near the keyword 'nonclustered'.

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

Answer 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.

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     
)

Answer 2

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

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)