CODEWITHSUNDEEP
sqlsql-server
Msd Manikandan Karur
Msd Manikandan Karur

Reputation: 41

Is it possible non-Clustered Index while creating Table?

create table tblPeople 
(
     sno int  primary key ,  
     Name nvarchar(50), 
     Gender nvarchar(10) , 
     Age int ,
     index IX_tblPeople_Age nonclustered(age)
)

I'm getting an error when executing above.

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Upvotes: 3

Views: 4695

Answers (3)

Martin Smith
Martin Smith

Reputation: 453233

You can achieve the same result using inline syntax compatible with 2008 with

create table tblPeople 
(
     sno int  primary key ,  
     Name nvarchar(50), 
     Gender nvarchar(10) , 
     Age int ,
     Constraint IX_tblPeople_Age unique nonclustered(age, sno)
)

As sno will be the CI key and SQL Server always adds the clustered key to the keys of nonunique nonclustered indexes created on tables with a clustered index anyway.

Upvotes: 0

Emilio Lucas Ceroleni
Emilio Lucas Ceroleni

Reputation: 1598

According to the CREATE TABLE documentation, this syntax works on 2014 versions of SQL Server and later. You can find the following piece of information in the article:

INDEX *index_name* [ CLUSTERED | NONCLUSTERED ] (*column_name* [ ASC | DESC ] [ ,... *n* ] )

Applies to: SQL Server 2014 through SQL Server 2017 and Azure SQL Database.

Specifies to create an index on the table. This can be a clustered index, or a nonclustered index. The index will contain the columns listed, and will sort the data in either ascending or descending order.

So if your version is prior to 2014 I would suggest this syntax:

CREATE TABLE tblPeople (
 sno int  primary key ,  
 Name nvarchar(50), 
 Gender nvarchar(10) , 
 Age int
) 
GO
CREATE NONCLUSTERED INDEX IX_tblPeople_Age ON tblPeople (age);

Upvotes: 1

digital.aaron
digital.aaron

Reputation: 5707

Could you just use the traditional syntax of creating the index after the table?

CREATE TABLE tblPeople (
     sno int  primary key ,  
     Name nvarchar(50), 
     Gender nvarchar(10) , 
     Age int
) 
CREATE NONCLUSTERED INDEX IX_tblPeople_Age ON tblPeople (age);

Upvotes: 1

Related Questions