Aaron Bertrand

A Big Deal : SQL Server 2016 Service Pack 1

November 16, 2016 by in SQL Server 2016 | 9 Comments
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download

Featured Author

Erin Stellato is a Principal Consultant with SQLskills and a Microsoft Data Platform MVP.

Erin’s Posts

Well, I can't claim that you heard it here first, since it was announced today by Scott Guthrie (@ScottGu) at Connect(); // 2016 and also on the SQL Server team blog, but SQL Server 2016 Service Pack 1 is the build of SQL Server that will finally make just about everyone upgrade from all previous versions.

Why? Well, I hope you're sitting down:

Many Enterprise features are now available in Standard, Web, and Express.

This is a BIG DEAL.

I've long been an advocate of making the surface area the same across all editions, and restricting each edition to hardware/resource limits. The latter is already the case with Express Edition (1 CPU, 1 GB memory, 10 GB database size) and Standard Edition (24 cores, 128 GB), but features themselves simply don't exist there. While they did add some functionality around Availability Groups (limited support in Standard Edition, and the ability to forego a domain), many of the features that are currently Enterprise only don't seem to be Enterprise-y at all. Here are some reasons and consequences of putting so many critical features only in Enterprise, at least in my opinion:

  1. Data security seems to have taken a back seat in recent years. It should be in all editions; I was disappointed when Always Encrypted was released and it was restricted to Enterprise. The implicit message was that, if you're a small business, you don't need to be able to encrypt credit card data (they say the same with Transparent Data Encryption, but Always Encrypted is a much more valuable offering).
  2. Programmability features that moonlight as performance features – like data compression and partitioning – are arguably on the Enterprise side of things, even though I've always felt data compression's sweet spot is the small business who can't afford to just add memory, buy faster disks, or upgrade to Enterprise.
    In current versions, when a vendor wants to take advantage of compression or partitioning, they have to consider that not all of their customers will be using Enterprise Edition. Similarly, customers want to use the same codebase in their QA, production, and development environments, but those may not all have the same edition either. Both vendors and customers also want to be able to use features that are available in both cloud and box, regardless of tier or edition. Currently, they either need to write multiple codebases, or simply surrender to the lowest common denominator.
  3. Adoption rates are slow because, in many respects, Standard Edition upgrades have been far from compelling in recent years, meaning many people are content with their 2012 Standard instances. They just don't really gain much from moving to a more recent version and, in many cases, the cost of testing a migration and dealing with regressions actually outweighs any benefits.

Colleagues like Brent Ozar and Steve Jones have had similar thoughts – arguing that the best features remain excluded from Standard and lower editions for revenue reasons, but this isn't the way it should be.

And now it's not.

Feature Standard / Web Express LocalDB
Always Encrypted
Change Data Capture
Columnstore 1
Data Compression
Database Snapshots
Dynamic Data Masking
Fine-Grained Auditing
In-Memory OLTP 2
Multiple Filestream Containers
Row-Level Security

1. Limited to 32GB per instance on Standard Edition, 352MB on Express.
2. Limited to 32GB per database on Standard Edition, 352MB on Express.

I don't have any evidence of this, but I believe that due to slow adoption rates of SQL Server 2016 – and Standard Edition specifically – Microsoft has finally listened. Perhaps they've realized that making so many compelling features only available in Enterprise Edition doesn't work. Can't get blood from a stone, they say.

The table here shows the set of features now enabled outside of Enterprise Edition (some features were already there in Standard, but I didn't have a good way to illustrate that). It only has to be a table because of three features that don't work on Express and/or LocalDB (due to SQL Server Agent, permissions, or external dependencies).

That's my quick cheat sheet; check the documentation for the latest updates. I've tested most of the features and they work as you would expect – not to trivialize the effort, but they just removed the checks that blocked functionality based on edition. I thought I would trip them up by testing beyond the basics, like incremental statistics on partitioned tables and combinations like Columnstore over a compressed table, to see if they missed something. But no – everything in the table worked just as if I were running Enterprise Edition.

That said, the focus here was for a consistent programming surface area (CPSA). So no, you won't suddenly be getting all of the benefits of Enterprise Edition for free or at a substantial discount – see below for functionality that won't work. And some of the features above may still be scale-limited based on edition.

Nonetheless, this does provide a great solution to #2 above: Having many of these features work in all editions, even if they support different levels of scale, will make it easier for both customers and vendors to build solutions that work no matter where they're deployed. This is going to be a huge win, even beyond the pure benefit of finally being able to use things like Always Encrypted and partitioning everywhere.

What Still Differentiates Editions?

Essentially, if it was an Enterprise-level operational or availability feature before, or within a certain set of performance features, it will remain as such. The following is not an exhaustive list, but probably captures most of the big differences:

  • Availability features like online operations, piecemeal restore, and fully functional Availability Groups (e.g. read-only replicas) are still Enterprise only. I wish the REBUILD + (ONLINE = ON) syntax could work as a no-op in lower editions with a warning instead of returning an error, but I can't be too fussy here.
  • Performance features like parallelism still won't work in Express Edition (or LocalDB). Automatic indexed view usage without NOEXPAND hints, and high-end features like hot-add memory/CPU, will continue to be available only in Enterprise.
  • Operational features like Resource Governor, Extensible Key Management (EKM), and Transparent Data Encryption will remain Enterprise Edition only. Others, like Backup Encryption, Backup Compression, and Buffer Pool Extension, will continue to work in Standard, but will still not function in Express.
  • SQL Server Agent is still unavailable in Express and LocalDB. As a result, and as noted above, Change Data Capture will not work. Cross-server Service Broker also remains unavailable in these editions.
  • In-Memory OLTP and PolyBase are supported in Express, but unavailable in LocalDB.
  • Virtualization Rights haven't changed and are still much more valuable in Enterprise Edition with Software Assurance.
  • Resource limits on downlevel editions remain the same. Seems the bean-counters still control the upper memory limit in Standard Edition, so this is still 128 GB (while Enterprise Edition is now 24 TB). Personally, I think this upper limit should make incremental gains with each new version (Moore's Law), because I feel that Standard Edition is expensive enough that its memory limits should never be so dangerously close to the upper bound of a well-equipped laptop. But I'll take what I can get, and when you are stuck on Standard Edition and scale is required, you can now use the above Enterprise features across multiple Standard Edition boxes or instances, instead of trying to scale up.


This is a bold move by Microsoft, and I can certainly see how this will increase Standard sales without cannibalizing Enterprise sales. Standard Edition customers can use these features both to consolidate their codebases and, in many scenarios, build solutions that offer better performance. And for those who already enjoy all the benefits of Enterprise Edition, this may not be all that interesting to you, but there are also a bunch of new features available across all editions of SP1. So regardless of what edition you're on now, you really have no excuse for holding on to that ancient version of SQL Server.

Go upgrade to SQL Server 2016 SP1!