With SQL Server 2014, New way to create NONCLUSTERED Index on Persisted Table Object

With SQL Server 2014 we can create NONCLUSTERED Index at table level while creating table object., Lets see how we can do by doing a small practical.

In SQL Server 2014

We are creating a table and trying to define NONCLUSTERED index at table creation.

create table testDemo

(col1 int primary key,

col2 int index idx_col2_testDemo,

col3 int index idx_col3_testDemo)

Command(s) completed successfully.

Since command has successfully executed, lets see indexes created by querying Sys.indexes Catalog view.

select name, index_id, type_desc

from sys.indexes where object_id = OBJECT_ID('testDemo')

name                           index_id    type_desc

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

PK__testDemo__357D0D3EF2E23FAC 1           CLUSTERED

idx_col3_testDemo              2           NONCLUSTERED

idx_col2_testDemo              3           NONCLUSTERED

By querying Sys.indexes we are sure, we have three indexes on testDemo table. So this is how you can create NC index at table creation.

Just for our approval, lets see whether the same syntax is working in SQL Server 2008?

In SQL Server 2008 R2

create table testDemo

(col1 int primary key,

col2 int index idx_col2_testDemo,

col3 int index idx_col3_testDemo)

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.

So here we saw in SQL Server 2008 R2 we don’t have option to created NC indexes at table creation.

The essence of code; it binds programmer in a non-fragile bond and never let his hand go without her!!!

Post Reference: Vikram Aristocratic Elfin Share