The Brighter Side of SQL Server’s future

SQL Server

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.

Previous Post
Initial Thoughts on 2016 SP1
Next Post
What’s the Difference Between Estimated and Actual Execution Plans?

9 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.