This week, I discovered a new enhancement which is not hidden but not really visible in SQL Server 2014. Among Hekaton and other new features in SQL Server 2014, a little option appears in table creation.8-O
On the msdn website, we can see a new section in the “CREATE TABLE” code:

Create_table_index_01.png

 

Create_table_index_02.png

We can directly create an index in the “CREATE TABLE” query. Ok, then let’s go!

In my example, I create a diver table with a default clustered index on a primary key (DiverId) and 2 other non-clustered indexes on Diving Level (OWD, AOWD, Level 1, Level 2,…) and Diving Organization (PADI, CCMAS, SSI,…).

Prior to SQL Server 2014, you create indexes after setting up the table like this.

Create_table_index_03.png

We need to have 3 instructions to create a table and 2 non-clustered indexes.

In SQL 2014, it is very easy with just one query:

Create_table_index_04.png

I’m very happy to share new features with you that are not necessarily in the light!:-D
You will find the MSDN “CREATE TABLE” reference for SQL Server 2014 here.