SQL Server 2016 SP1: Features Added to Standard, Web, Express, Local DB Editions

that-time-sql-server-gave-you-a-ponyWouldn’t it be awesome if you could develop your application for a small SQL Server using the same features that you intend to use for scale?

And wouldn’t it be more awesome if you could start using bleeding edge features like In-Memory OLTP on some of your less-risky, smaller databases first?

In the past, cost inhibited feature adoption

This has been tough in the past, because SQL Server limits which Edition can use specific programmability features. Enterprise Edition gets all the goodies, and Enterprise costs more:

  • Enterprise Edition is ~$7,000 USD/core (minimum 4 cores per socket, sold in 2 core packs)
  • Standard Edition is ~$1,800 USD/core (minimum 4 cores per socket, sold in 2 core packs / also available for purchase by Client Access License)

With these prices, developers cant’t usually afford to use Enterprise Edition features on new, small projects. And they were forced to introduce cutting edge EE features into the most critical databases first– because the least critical databases don’t get those licensing dollars. Software vendors often have to maintain two versions of their SQL Server codebase: one for Standard Edition customers, and one for Enterprise Edition customers.

That changes today.

Today is the Microsoft Connect() online developer conference. It’s also the day that SQL Server 2016 Service Pack 1 is dropping. SP1 includes some amazing licensing changes.

Download SQL Server 2016 SP 1 here.

Data Management Features added to Standard, Express, Web, and (mostly) Local DB Editions starting with SQL Server 2016 SP1

Developers will now be able to use far more features in production, whether or not they’re using Enterprise Edition. These features are now available in “lower” editions.

I’ve noted usage “caps” as I understand them from initial information:

  • Table Partitioning
  • Data Compression
  • Columnstore Indexes
    • Standard Edition limits DOP to 2
    • Web / Express limit DOP to 1.
    • Memory limited to 25% Buffer Pool limit for each non-EE edition (so you get 32GB for Standard Edition)
  • In-Memory OLTP
    • Memory limited to 25% Buffer Pool limit for each non-EE edition (so you get 32GB for Standard Edition)
    • Not in Local DB
  • Distributed Partitioned Views (writeable)
  • Multiple Filestream Containers
  • Change Data Capture
    • Not in Express or Local DB, as this requires SQL Server Agent, which is not present there
  • Database Snapshots
  •  PolyBase
    • Exception: not in Local DB

Security Features added to Standard, Express, Web, and (mostly) Local DB Editions starting with SQL Server 2016 SP1

At this time I don’t know of any limitations on these features, or differences from Enterprise. It’s very cool to see SQL Server making enterprise grade security features available to everyone, in every edition.

  • Auditing (some)
  • Always Encrypted
  • Row-Level Security
    • Has been available in Standard in 2016, now in Web, Express, Local DB
  • Dynamic Data Masking
    • Has been available in Standard in 2016, now in Web, Express, Local DB

dev-environmentDid the price for Standard Edition go up?

Nope. No pricing changes. Just wider feature availability!

Will people still need to buy Enterprise Edition?

Yes. Scalability limits for edition aren’t changing. Enterprise still gets you more memory and more CPUs to scale your workload.

Fine Print: not all features have licensing changes

The clever reader may have noticed that I haven’t mentioned AlwaysOn Availability Groups, Transparent Data Encryption, Database Mirroring, or Peer-to-Peer Replication. No licensing changes have been announced for those features, or for services like SSAS or SSRS.

This is a huge licensing improvement, and it’s targeted at how you architect your schema and your codebase, and how you secure your data. But it doesn’t touch everything.

Don’t worry, Developer Edition still acts like Enterprise

Note that these changes don’t apply to Developer Edition. It continues to have all the features of Enterprise Edition, and it’s free (aww yeah), but it’s only for non-production environments.

Seems like a great time to upgrade to 2016, doesn’t it?

Traditionally, managers like to wait until Service Pack 1 comes out before they get serious about upgrading to a new SQL Server version. You’ve got an especially good reason to talk about upgrades with this one!

PS: you try out SQL Server v.Next on Windows and Linux, too

There’s so much cool news today that I’m weirdly including this as a final note. The first preview of SQL Server v.Next is dropping today. You can run it on Windows. You can run it on Linux. You can run it in a Docker container on a Mac.

Download v.Next CTP1 it here.


Previous Post
Downgrading the SQL Server Edition of a Dev Environment
Next Post
Should I Upgrade to SQL Server 2016? (Dear SQL DBA Episode 22)

Related Posts

No results found

6 Comments. Leave new


I noticed that the SQL 2016 sp1 Edition feature compare site here: https://www.microsoft.com/en-us/sql-server/sql-server-editions
Shows one less feature for SQL 2016 sp1 Standard Edition – stretch db
than is shown here: https://technet.microsoft.com/en-us/windows/cc645993(v=sql.90)

Can you confirm/deny Stretch Database feature availability for SQL 2016 sp1 standard?



    Hey John,

    Thanks for your question– and great eye! I confirmed from Microsoft that it is a typo on the new page. Stretch DB remains available in all editions. They’ll work on updating the page and they said to tell you thanks for letting them know.


Alexandre Araujo
November 30, 2016 6:18 am

Hi Kendra,

I’ve noticed the web edition still being a good option for some environments thus my question is about prices.

Whereas that the standard edition costs $1,800 USD/core, the web edition is offers only by hostings or can i buy the same ways as std ? if is it possible how is the proximity price ?


Alexandre Araujo


Hi Kendra
Is max server memory still be upper bound limit for sql server instance?
In case of max server is limit for all, I have sql server 2016 standard which set max server to 58gb, all buffer pool, columnstore, in-memory oltp located and etc are located on 58 gb. Can we set or limit buffer pool size?
If we can limit, sql server has also limit size of in-memory oltp to 25% of buffer pool size or not.

Pisit Changmai


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.