Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inline syntax for indexes Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 6:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 10:29 PM
Points: 98, Visits: 31
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)
);

Post #1601814
Posted Monday, August 11, 2014 10:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 37,080, Visits: 31,640
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1601961
Posted Monday, August 11, 2014 12:22 PM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1602015
Posted Monday, August 11, 2014 12:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 37,080, Visits: 31,640
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1602025
Posted Monday, August 11, 2014 1:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1602027
Posted Monday, August 11, 2014 4:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 37,080, Visits: 31,640
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1602109
Posted Monday, August 11, 2014 11:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 10:29 PM
Points: 98, Visits: 31
Gila, thanks a lot! This is very convenient syntax. :)
Post #1602147
Posted Tuesday, August 12, 2014 1:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 37,080, Visits: 31,640
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1602452
Posted Tuesday, August 12, 2014 2:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1602476
Posted Friday, August 15, 2014 6:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 37,080, Visits: 31,640
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1603940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse