By: Ben Snaidero
Overview
When it comes to feature sets within the software, because SQL Server is such a widely used product, Microsoft uses both the version of the software as well as editions within each version to control the features that are available. This model allows large companies to have all the performance and high availability functionality they require as well as small companies who don't require the same level of performance to use the same software base.
As you'd expect with SQL Server, each new release has brought many new features over the years. Below are some of the highlights from the last few releases. A complete listing can be found in the links in the additional information section.
- SQL Server 2014
- Memory-Optimized Tables
- Encryption for Backups
- New Design for Cardinality Estimation
- Clustered columnstore indexes
- Buffer Pool Extension
- SQL Server 2016
- Temporal tables
- Always Encrypted
- Query Store
- R Services
- SQL Server 2017
- Adaptive query processing
- Resumable online index rebuild
- Automatic database tuning
Microsoft also has multiple editions available within each version. There are 5 edition levels and each of the levels are geared towards different sized companies/applications. Below is a short description of each edition.
- Enterprise - contains all features with high end datacenter capabilities needed in large enterprises
- Standard - has basic data management which is geared towards departments or small companies
- Web - is a low cost option that web hosting companies can offer to their customers
- Developer - contains all of the functionality in enterprise edition, but is only licensed for development and test systems. This is an ideal version for developers who build applications.
- Express - free entry level database ideal for learning how to build a data driven application or for very small databases
A lot of the differences in each edition are hardware/performance based. That said there are some functional differences between editions as well, so you need to be careful when selecting an edition. For example, when it comes to database mirroring although it's available in both Enterprise and Standard edition, in Standard edition you can only use "Full Safety" mode (synchronous), "High Performance" mode (asynchronous) is not available.
The following table shows some examples of this and the links in the additional information section contain a complete listing.
| Feature | Enterprise | Standard | Web | Express |
|---|---|---|---|---|
| Max Compute Capacity | OS max | Lesser of 4 sockets or 24 cores |
Lesser of 4 sockets or 16 cores |
Lesser of 1 socket or 4 cores |
| Max Buffer Pool Memory | OS max | 128 GB | 64 GB | 1410 MB |
| Max Database Size | 524 PB | 524 PB | 524 PB | 10 GB |
We won't go into too much detail regarding upgrades and moving between editions in this tutorial as that could be a tutorial topic all on its own, but Microsoft does provide tools that make this a fairly straightforward task. You can read more on how to do this here. Choosing a SQL Server Upgrade Method
Additional Information
- What's New in SQL Server 2017
- What's New in SQL Server 2016
- What's New in SQL Server 2014
- Editions and Components of SQL Server 2017
- Editions and Components of SQL Server 2016
- Editions and Components of SQL Server 2014
Last Update: 8/23/2019
