We know from our experience with previous versions of SQL Server’s that there are two ways to create an index in a table.

One is to Right click the table at Object Explorer level and create it.

Second is to explicitly write a T-SQL statement starting with Create Index on Table name after the create table statement.

There is a single pitfall in the above two methods, the index cannot be created simultaneously within table definition like a Primary key or Foreign key constraint. SQL Server 2014 overcomes this draw back by facilitating to create an index within the Create table definition which is referred to as Inline Specification for Index creation.

Hence the index could be defined as below within the Create table statement,

CREATE TABLE DBO.FRUITSHOP (

ITEMNAME VARCHAR (25) NULL,

QUANTITY INT NOT NULL,

UNITPRICE FLOAT NOT NULL,

INDEX IX_RATE NONCLUSTERED (ITEMNAME, QUANTITY) )

The above code will create a Non Clustered Index named IX_RATE and can be checked from SYS.INDEXES table by using the below query.

SELECT * FROM SYS.INDEXES WHERE NAME=‘IX_RATE’

However the  above create table code will fail in SQL Server 2012 and earlier versions with the following error.

Msg 1018, Level 15, State 1, Line 7

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.

Hence 2014 makes our coding easier. The Inline specification can be extended for Clustered and Non Clustered Indexes with varying number of columns as needed and can be found in MSDN site.

Hope it helps!