A new way to create Non Clustered composite index in SQL Server 2014

In Earlier version of SQL Serve i.e SQL Server 2008, SQL Server 2012, we were unable to create Composite NONCLUSTERED index on table level, i.e while creating table.

Creating NONCLUSTERED composite Index on SQL Server 2008R2

create table test1

(col1 int primary key clustered,

col2 int index idx_nc(col2, col2),

col3 int

)

Msg 1018, Level 15, State 1, Line 3

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.

Earlier version of SQL Server does not allow us to create Composite NONCLUSTERED index on table level. So to create Composite index, we were writing separate statement of Create INDEX, see below

First create table

create table test1

(col1 int primary key clustered,

col2 int,

col3 int

)

Command(s) completed successfully.

Then once the table is ready, create composite index on columns.

create nonclustered index idx_test1_col2_col3

on test1(col2,col3)

Command(s) completed successfully.

But with the advancement of SQL Server 2014, it allow us to create Composite NonClustered Index on table level, lets see how we can create Composite index in SQL Server 2014 at table level.

Creating NONCLUSTERED Composite Index at Table Level in SQL Server 2014

create table test1

(col1 int primary key clustered,

col2 int index idx_nc(col2, col3),

col3 int

)

Command(s) completed successfully.

Querying Sys.Indexes to check the indexes created.

select name, type_desc from sys.indexes

where object_id = OBJECT_ID('test1')

name                         type_desc

---------------------------- --------------

PK__test1__357D0D3EAC78055D  CLUSTERED

idx_nc                       NONCLUSTERED

Your code is so flexible, u can twist it, blend it and fit it to ur client wants, it has the potential of likeliness to patrons needs.

Post Reference: Vikram Aristocratic Elfin Share