Inline syntax for indexes

Question

Inline syntax for indexes

sektor81

Say Hey Kid

Points: 679
More actions
August 11, 2014 at 6:01 am

#291883

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)
);

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

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

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)

sektor81 Say Hey Kid Points: 679 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

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)