CREATE TABLE and Indexes
-
Comments posted to this topic are about the item CREATE TABLE and Indexes
-
This was removed by the editor as SPAM
-
Thanks Steve!
I strongly feel that Qotd's like these, is the best way to learn changes made to the product over various versions.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks Steve for this question. I learned something new about the inline indexes. I found a good description in MSDN here:
https://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx
-
Guessed wrong. I need to upgrade to 2016.
-
Oh, I missed the part about this being SQL Server 2016. When I run this on our 2008 R2 instance, I get:
Msg 1018, Level 15, State 1, Line 5
Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Oh well.
-
The question now is, did they update the script generator to reflect this new capability? Probably not. I also think it's interesting that they'd spend time on such a thing but not spend time on actual "Create or Replace" syntax. Yeah, they did something else that helps but what's the "standard"?
Did they, by any chance, also make it so that you can alter a synonym? No.
MS seems to make "improvements" based on what will sell the best but then they don't pay any attention to details when they do. We have a new splitter that won't return the ordinal position of the split out elements and returns nothing if you pass it a null. We still don't have a high performance sequence generator (built in Tally Function).
Stuff like this really makes me ask "what were they thinking"?
--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) -
Missed the 2016 part and compared to 12
Oh well, learnt something new
- Damian
-
Interesting, very interesting. Thanks, Steve!
-
Nifty, too bad I don't create tables often enough to care much about this vs just declaring the indexes separately. Especially if SSMS wasn't also updated to script out indexes inline.
-
Iwas Bornready (11/3/2016)
Guessed wrong. I need to upgrade to 2016.Version SQL Server 2014 would be enough. Inline specification of indexes
was introduced in version SQL Server 2014, see my link above.
-
Agreed!
-
Jeff Moden (11/3/2016)
Stuff like this really makes me ask "what were they thinking"?My guess is that they were thinking about marketing, advertising and long lists of new functionality. After all, the "reasons to change to SQL Server X" appears to be greater when it's a HUGE list of features. Now, the individual features being incomplete is something they won't address - after all, they're brand new features. 😉
-
Jeff Moden (11/3/2016)
... I also think it's interesting that they'd spend time on such a thing but not spend time on actual "Create or Replace" syntax.Create or Replace would be a beautiful thing.
-
Marcia J (11/7/2016)
Jeff Moden (11/3/2016)
... I also think it's interesting that they'd spend time on such a thing but not spend time on actual "Create or Replace" syntax.Create or Replace would be a beautiful thing.
Yes, it would. I really missed it from my Oracle days, but have gotten used to not having it. I'd like to see other things before they spend time on that, but I doubt they'd spend time on them.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply