SQL Server Features Comparison
As always there are so many new features in the newest release of SQL Server, SQL Server 2016, therefore this article cannot cover all of them. However, we have selected those new features that are more relevant.
You can see a SQL Server 2016 and 2014 features comparison in the table below:
| FEATURES | SQL Server 2016 | SQL Server 2014 | |
|---|---|---|---|
| Performance | In-Memory OLTP |  (1) | * |
| In-Memory ColumnStore for DW | (2) | * |
|
| Buffer Pool Extension to SSOs | | |
|
| Enhanced virtualization support and Live Migration | | |
|
| Real-time Operational Analytics | |  |
|
| Query Data Store | | |
|
| Native JSON Support | | |
|
| Multiple TempDB Database Files | | |
|
| Temporal Tables | | |
|
| Availability | Always On | (3) | * |
| Database Recovery Advisor | | |
|
| Support for Windows Server Core | | |
|
| Security | Transparent Data Encryption (TDE) | | * |
| Backup Encryption Support | | |
|
| Enhanced Separation of Duties | | |
|
| Always Encrypted | | |
|
| Row Level Security | | |
|
| Dynamic Data Masking | | |
|
| Data Access | Enhanced Reporting Services | | |
| Enhanced Mobile BI Apps | | |
|
| PolyBase | | |
|
| R comes to SQL Server | | |
|
| Built-in Advanced Analytics | | |
|
| Cloud-Readiness | Simplified Cloud Back-Up to Microsoft Azure | | |
| SQL Server VMs and memory sizes available in Microsoft Azure | | |
|
| Gallery of VM images in Microsoft Azure | | |
|
| Stretch Database | | |
|
| Management & Programmability | Policy Based Management | | |
| Distributed Replay | | |
* SQL Server Enterprise Edition only
(1) In-memory OLTP enhancements: Greater T-SQL surface area, 2 terabytes of memory supported (256 GB in SQL Server 2014) and greater number of parallel CPUs. Enhanced in-memory performance with up to 30x faster transactions.
(2) In-Memory ColumnStore for DW: More than 100x faster queries than disk based relational databases.
(3) Enhanced Always On:
- Up to 3 synchronous replicas for auto failover across domains
- Round-robin load balancing of replicas
- DTC & SSIS support
- Automatic failover based on database health
Below the new features added in SQL Server 2016 are explained in more detail:
PERFORMANCE:
Real-time Operational Analytics:
Insights on operational data, works with in-memory OLTP or on disk.
This feature uses the dynamic duo of SQL Server’s in-memory technologies. It combines In-Memory OLTP with the In-Memory columnstore for Real-time Operational Analytics.
Its purpose is to tune your system for optimal transactional performance as well as increase workload concurrency.
This sounds like a great combination and applying analytics to your system’s performance is something a lot of customers have asked for a long time but you will certainly need to have the memory to take advantage of it.
Query Data Store:
This new feature provides DBAs with insight on query plan choice and performance.
In previous versions to SQL Server 2016, you need to use Dynamic Management Views (DMVs) in order to examine existing execution plans.
The problem is that DMVs only allow you to see the plans that are actively into the plan cache. In other words, you cannot see any history for plans once they are rolled out of the plan cache.
With the Query Data Store feature, SQL Server now saves historical execution plans. It is more, SQL Server also saves the query statistics that go along with those historical plans.
This is a great addition and will allow you to now track execution plans performance for your queries over time.
Native JSON Support:
JSON (Java Script Object Notation) is a standardized data exchange format that is currently not supported natively by SQL Server. To perform JSON imports and exports you need to hand-code complex T-SQL, SQLCLR or JavaScript.
JSON support allows you to interchange data between applications and the SQL Server database engine.
By adding this support, Microsoft has provided SQL Server the ability to parse JSON formatted data so it can be stored in a relation format. Additionally, you can take relational data and turn it into JSON formatted data.
Microsoft has also added some new functions to provided support for querying JSON data stored in SQL Server.
Having these additional JSON features built into SQL Server should make it easier for applications to exchange JSON data with SQL Server.
Multiple TempDB Database Files
This way allows you to have more than one TempDB database file if you are running on a multicore machine.
By using SQL Server 2014, you had to manually add additional TempDB files after installing SQL Server.
With SQL Server 2016 you can now set the number of TempDB files you need while you are installing SQL Server.
Having this new feature means that you no longer have to manually add additional TempDB files after installing SQL Server.
Temporal Tables:
A temporal table is a new type of table that provides correct information about stored facts at any point in time.
Each temporal table consists of two tables actually, one for the current data and one for the historical data. Both tables are linked.
By having temporal tables SQL Server can automatically manage moving old row versions to the temporal table every time a row in the base table is updated.
The system ensures that when the data changes in the table with the current data the previous values are stored in the historical table.
SECURITY:
Always Encrypted:
Always Encrypted is designed to protect data at rest or in motion.
With the Always Encrypted feature enabled your SQL Server data will always be encrypted within SQL Server.
Encryption and decryption of data happens transparently inside the application. Access to encrypted data will only be available to the applications calling SQL Server.
Always Encrypted enables client application owners to control who gets access to see their applications confidential data. It does this by allowing the client application to be the one that has the encryption key. That encryption key is never passed to SQL Server.
By doing this you can keep those nosey Database or Windows Administrators from poking around sensitive client application data In-Flight or At-Rest.
This feature will allow you to keep calm knowing your confidential data stored in a cloud managed database is always encrypted and out of the eyes of your cloud provider.
Row Level Security:
By using the feature Row Level Security, the SQL database engine is able to restrict access to row data, based on a SQL Server login.
Restricting rows will be done by filter predicates defined in inline table value function. Security policies will ensure the filter predicates get executed for every SELECT or DELETE operation.
Implementing Row Level Security at the database layer means application developers will no longer need to maintain code to restrict data from some logins, while allowing other logins to access all the data.
With this new feature, when someone queries a tables that contains Row Level Security they will not even know whether or not any rows of data were filtered out.
Dynamic Data Masking:
Dynamic Data Masking allows you to obscure confidential columns of data in a table to SQL Server for users that are not authorized to see the all the data. Also you can choose how the data will be obscured.
By setting up Dynamic Data Masking you can define a masking rules so unauthorized logins can only a part of the confidential data, whereas authorized logins can see all information.
DATA ACCESS:
PolyBase:
PolyBase allows you to use Transact-SQL (T-SQL) statements to access data stored in Hadoop or Azure Blob Storage and query it in an ad-hoc fashion.
By using PolyBase you can now write ad-hoc queries to join relational data sets stored in SQL Server with semi-structured data stored in Hadoop, or SQL Azure blob storage.
This allows you to get data from Hadoop without knowing the internals of Hadoop.
Therefore, you can leverage SQL Server’s on the fly column store indexing to optimize your queries against semi-structured data.
As organizations spread data across many distributed locations, PolyBase will be a solution for them to leverage SQL Server technology to access their distributed semi-structured data.
R comes to SQL Server:
Microsoft have incorporated R inside of SQL Server 2016 to support advance analytics against big data due to the purchase of Revolution Analytics.
By integrating R processing into SQL Server, data scientists will be able to take their existing R code and run it inside the SQL Server database engine.
This will eliminate the need to export your SQL server data in order to perform R processing against it.
Incorporating this new feature makes processing R is closer to the data.
Built-in Advanced Analytics:
Built-in Advanced Analytics provide the scalability and performance benefits of running your predictive analytics algorithms directly in SQL Server.
It also expands your R script library with Microsoft Azure Marketplace.
CLOUD-READINESS:
Stretch Database:
The Stretch Database feature allows you to dynamically stretch your on-premise database to Azure.
This allows frequent data access or remain in hot and cold facilities or access infrequently are moved to the cloud based on the cost of the actual availability.
This allows you to take advantage of low cost Azure store and still have high performance applications.
It works with Always Encrypted and Row Level Security.
