CODEWITHSUNDEEP
sql-serversql-server-2008
Rasshme
Rasshme

Reputation: 1641

How to create nonclustered index in 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'.

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

Upvotes: 19

Views: 92394

Answers (2)

Svisstack
Svisstack

Reputation: 16616

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)

Upvotes: 14

edosoft
edosoft

Reputation: 17271

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     
)

Upvotes: 20

Related Questions