Inline syntax for indexes

  • I wonder whether there's documentation about inline syntax for indexes for SQL Server 2014? I couldn't find it here.

    CREATE TABLE Consumer

    (

    Account nvarchar(20) null,

    Consumption float null,

    INDEX IX_Consumer_Account NONCLUSTURED (Account)

    );

  • sektor81 (8/11/2014)


    I wonder whether there's documentation about inline syntax for indexes for SQL Server 2014? I couldn't find it here.

    CREATE TABLE Consumer

    (

    Account nvarchar(20) null,

    Consumption float null,

    INDEX IX_Consumer_Account NONCLISTURED (Account)

    );

    The only indexes that can be created during table creation are those that will be created by unique constraints. For example, PK's.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You're looking on the wrong page, the page you linked to is for Azure databases, where I assume this feature doesn't exist. If you look at the CREATE TABLE page for SQL Server 2014 (the non-Azure version), the inline syntax is listed.

    http://msdn.microsoft.com/en-us/library/ms174979%28v=sql.120%29.aspx

    CREATE TABLE

    [ database_name . [ schema_name ] . | schema_name . ] table_name

    [ AS FileTable ]

    ( { <column_definition> | <computed_column_definition>

    | <column_set_definition> | [ <table_constraint> ]

    | [ <table_index> ] [ ,...n ] } )

    [ ON { partition_scheme_name ( partition_column_name ) | filegroup

    | "default" } ]

    [ { TEXTIMAGE_ON { filegroup | "default" } ]

    [ FILESTREAM_ON { partition_scheme_name | filegroup

    | "default" } ]

    [ WITH ( <table_option> [ ,...n ] ) ]

    [ ; ]

    < table_index > ::=

    INDEX index_name [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )

    [ WITH ( <index_option> [ ,... n ] ) ]

    [ ON { partition_scheme_name (column_name )

    | filegroup_name

    | default

    }

    ]

    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

    }

    Main use I think for this will be table variables where you no longer have to jump through hoops to get non-unique indexes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Now that would be nice in "regular" T-SQL. Thanks for jumping in, Gail.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/11/2014)


    Now that would be nice in "regular" T-SQL.

    Err.. It is in regular T-SQL, from SQL 2014 onwards.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/11/2014)


    Jeff Moden (8/11/2014)


    Now that would be nice in "regular" T-SQL.

    Err.. It is in regular T-SQL, from SQL 2014 onwards.

    I'm so far behind that I'll need to be twins to catch-up. Most of the folks that I've been working with are still working with 2005 and I haven't given a thought to catching up to 2014, yet. Seems like I can't convince them to upgrade, either. Thanks for the info.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Gila, thanks a lot! This is very convenient syntax. 🙂

  • On that note, I do wish that MS had devoted dev time to something more important instead of this "feature".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The inline syntax is needed for hekaton tables because they're like table vars in that once created they cannot be changed. The fact that it works on normal tables as well is a bonus (probably would have been harder to develop it to only work on hekaton tables than all tables, since the create table syntax is very similar between the two)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/12/2014)


    The inline syntax is needed for hekaton tables because they're like table vars in that once created they cannot be changed. The fact that it works on normal tables as well is a bonus (probably would have been harder to develop it to only work on hekaton tables than all tables, since the create table syntax is very similar between the two)

    Thanks a lot, Gail. I appreciate the info especially as to the "why".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply