SQL Server Version Comparison

In this blog, lets understand the SQL Server version comparison of last three versions 2016, 2017 and 2019.

SQL Server Version Comparison
Features201620172019
Database performance
In-memory database: in-memory OLTP YesYes
In-Memory Database: Persistent MemoryYesYesYes
In-memory database: memory-optimised tempdb  Yes
Real-time operational analyticsYesYesYes
Intelligent query processing YesYes
Intelligent performance  Yes
Availability
Always onYesYesYes
Accelerated database recovery  Yes
Read scale-out availability groupsYesYesYes
Security
Always encryptedYesYesYes
Always encrypted with secure enclaves  Yes
Data discovery and classification  Yes
Transparent data encryptionYesYesYes
Backup encryption supportYesYesYes
Encryption at rest and in motionYesYesYes
Dynamic data masking and row-level securityYesYesYes
Management and programmability
Linux support YesYes
Container support YesYes
Kubernetes support  Yes
Temporal tablesYesYesYes
JSON supportYesYesYes
Graph data support YesYes
UTF 8 support  Yes
Java support  Yes
Azure Data Studio to manage SQL Server including support for T-SQL using notebooksYesYesYes
Database compatibility certificationYesYesYes
Intelligence across all your data
Big Data Clusters  Yes
Data virtualisation using PolyBaseYesYesYes
Cloud readiness
Backup to AzureYesYesYes
Disaster recovery to AzureYesYesYes
Optimised virtual machine (VM) images in Azure galleryYesYesYes
Free asynchronous replication on Azure Virtual Machines for disaster recovery  Yes
BI and Analytics Support
Integration services managed as a serverYesYesYes
Tabular BI semantic modelYesYesYes
Master data servicesYesYesYes
Data quality servicesYesYesYes
Many-to-many relationships in tabular models  Yes
End-to-end mobile BI on any deviceYesYesYes
SQL Server Analysis Services (SSAS) direct queryYesYesYes
Calculation groups in tabular model  Yes

Older version comparison

Features20172016201420122008 R2
Performance     
In Memory OLTPYesYesYes  
In-Memory ColumnStoreYesYesYesYes 
Real Time Operational AnalyticsYesYes   
Buffer Pool extension to SSDYesYesYes  
Adaptive Query ProcessingYes    
Availability     
Always OnYesYesYesYes 
Basic Availability GroupsYesYes   
Security     
Transparent data encryptionYesYesYesYesYes
Backup encryption supportYesYesYes  
Encryption at rest and in motionYesYes   
Dynamic Data Masking and Row Level SecurityYesYes   
Separation of dutiesYesYesYesYes 
Cloud-readiness     
Backup to AzureYesYesYesYes 
Disaster recovery to Microsoft AzureYesYesYes  
Optimized virtual machine images in Azure galleryYesYesYesYes 
Stretch DatabaseYesYes   
Management and programmability     
Runs on Linux and Docker ContainersYes    
Temporal tablesYesYes   
JSON supportYesYes   
Graph data supportYes    
BI and analytics     
Integration services managed as a serverYesYesYesYes 
PolyBase for T-SQL query across HadoopYesYes   
Tabular BI semantic modelYesYesYesYesYes
Master data servicesYesYesYesYesYes
Data quality servicesYesYesYesYes 
In-database advanced analyticsYesYes   
End-to-end Mobile BI on any deviceYesYes   

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply