Believe it or not, I sometimes like things
SP1 for 2016 has a bunch of cool new tricks:
I’m pretty psyched on the Wait Stats stuff in actual execution plans. I hope it eventually comes in some form to cached plans, or at least Query Store plans, but this is a good start! Even if it’s just averages, it’d be nice to know what plans were usually waiting on. Ditto the CPU and elapsed time. Brilliant additions that I can’t wait to see more of.
CREATE OR ALTER is so much nicer than the ol’ OBJECT_ID song and dance. I still can’t use it, on account of all the backwards compatibility our scripts have to deal with. Is there an EOL on SQL Server 2014 yet?
Getting IFI and LPIM info via DMVs is also pretty sweet. We’ll have those in Blitz soon, I’m sure, along with the Database Scoped configurations.
Overall, there’s a nice range of stuff that got added. It’s an exciting time to be working with SQL Server.
But oh wait, what’s that?
If you head over to the vNext page, there are two things on there that make downright amorous.
Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max)).
The STRING_AGG aggregate function has been added.
Oh my. That’s awesome. Beyond awesome. Right now, if you have tables with MAX datatype columns (and who doesn’t really?), you can either create nonclustered ColumnStore indexes around those columns, or you can move them off to another table and drop them from your main table to create a clustered ColumnStore index. Ask me about the night I spent converting the StackOverflow database to ColumnStore. I was so tired, I didn’t even do any experiments with it afterwards.
And that second one? STRING_AGG? Be still my heart. You’re killing me. It’s not just that it’s going to exist, it’s that I hope I had something to do with it existing. I may not have, but it feels nice to feel like you’re being heard. Right?
Are you there Microsoft? It’s me, Erik.
See, a while back I wrote a couple posts about Oracle and Postgres features I’d like to see in SQL Server. And while stuff like unlogged tables (my Connect Item for that got unceremoniously closed, but Brent’s is still alive) didn’t make it, some really cool stuff is in the pipeline. Being able to easily create delimited strings was one of the items in my Postgres post, and Adaptive Plans was something I mentioned in the Oracle post. Head on over to the link for some positing on what that might mean for SQL Server that I largely agree with. I’m imagining a world without parameter sniffing. It’s a beautiful place. It’s basically the party scene from PCU, except forever.
Again, I’m not saying I’m the reason for these features coming about. It’s just super cool to write about something, and then see it start to happen. Even though my Connect Item that got fixed doesn’t appear to be in SP1 or vNext, it’s still pretty rad that it will be in the future. Probably. In some form.
I got to talk to a few people from Microsoft at PASS, like Joe Sack, and they had some truly interesting things to talk about that are coming to SQL Server. Hopefully nothing that will put query tuners out of business, but hey, I’ve always wanted to take up water colors and drink wine all day. Buck Woody talked about R, and even though I didn’t understand any of it, it made me want to. I’m also probably lucky Kevin Farlee didn’t shiv me when I asked about the internals of Direct Seeding. Overall, it was a nice afternoon.
I think the functionality coming to SQL Server is great. Even if it’s not making it in there because I asked for it in a blog post, thanks for making me look all prescient and stuff!
And thanks for reading!
Brent says – For a while there, we were hearing that DBAs needed to learn Azure SQL DB or they were dead meat walking. Microsoft’s newfound love of the boxed product is a great sign that they understand the realities of development – some apps just don’t work in Azure SQL DB, and some companies just won’t adopt it – and there’s still a lot of real money to be made in the boxed product.
9 Comments. Leave new
Been waiting for something like STRING_AGG for some time. I’ve had many times where it would have been useful over the years, though mostly for one-off cases. I’d love it if it could make its way into a SQL 2016 update of some sort, but not counting on it, which likely means it stays off of my toolkit for another couple of years or whenever people really start upgrading past SQL 2016.
When people will upgrade is the most important part. It kind of stinks, but a lot of the adoption of latest-and-greatest comes from people starting new apps. People are still being pried off 2005, and only because it’s out of support.
The link over ‘Adaptive Plans’ goes to Brent’s unlogged table Connect request again.
All fixed! Thanks for letting me know!
STRING_AGG makes me feel kinda funny, like climbing the rope in gym class.
Because you didn’t get very far? 😀
I think Erik missed the Wayne’s World reference. 🙂
I have never missed a Wayne’s World reference in my life, sir.
It will be nice to retire our CLI-based string-concatenation function. But {sigh}… still no sign of some important things (been waiting for 6-9 years so far).
1. Passing handles to tables in/out of functions and sprocs (such that the data is read/write) – https://connect.microsoft.com/SQLServer/feedback/details/299296/relax-restriction-that-table-parameters-must-be-readonly-when-sps-call-each-other
2. Performant functions (UDFs have some bad side-effects) – https://connect.microsoft.com/SQLServer/feedback/details/524983/user-defined-function-performance-is-unacceptable
3. Declare variable types by referencing a table column (like Oracle has had for years) – https://connect.microsoft.com/SQLServer/feedback/details/520497/implement-equivalent-of-oracles-rowtype-attribute
Missing these basics are part of the reasons why I haven’t found any compelling reason to upgrade past Sql 2012. That introduced a bunch of awesome windowing functions, compressed backups (non-Enterprise), etc. But Sql 2014 was a {yawn}, and Sql 2016 was all Enterprisey and Cloudy. Sql 2016 SP1 is helping, though; the 128GB RAM for Standard Edition might put it over the top when I get time to re-evaluate.