Infrastructure at your Service

Stéphane Haby

SQL Server 2014 Tips: Create indexes directly via CREATE TABLE

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.

2 Comments

  • Usha says:

    Hi,
    This post helped me a lot. Thank you.

    I would like to know how to use the include statement along with this syntax.
    What I’m trying to do is moving the following index creation to inside table declaration.
    CREATE NONCLUSTERED INDEX ix_id ON #tmp([ID]) Include(col1,col2,col3) with (fillfactor = 100)

    I tried this

    INDEX ix_id NONCLUSTERED ([ID]) Include(col1,col2,col3) with (fillfactor = 100)
    But this gives some syntax error message. Could you please help?

    • StĂ©phane Haby says:

      Hi usha,

      it’s really a good question and unfortunately, it is not possible to create directly index with include(…) in the create table.
      This index needs to be created separately…
      Sorry, perhaps in the next version!

      Regards,
      Stéphane

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant