What's new in SQL Server version x

A frequent question I get is what the new stuff is in some version of SQL Server. And, sure, Microsoft has a very exhaustive "what's new" section for each version of SQL Server. The purpose of this page is to list all news on one page. Focus is on the SQL-engine, on-premise. 

Each version adds a new database compatibility level. Starting with SQL Server 2014 this can also affect performance, for instance enabling new functionality in the optimizer as well as controlling the Cardinality Estimation version used.

SQL Server 2019
https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15
  • Scalar UDF inlining
  • Improved error message when string is truncated
  • UTF-8 collations, for char and varchar, collation name end with UTF8 suffix.
  • Better diagnostics when query triggers and waits for statistics update
    • The command column show SELECT (STATMAN) in sys.dm_exec_requests
    • New wait type WAIT_ON_SYNC_STATISTICS_REFRESH
  • Static data masking (create a new database with masking persisted according to the masking functions specified)
  • Resumable online index create
    • Can also be made default through database scoped setting.
  • Build and rebuild clustered columnstore index online.
  • Always Encrypted with secure enclaves
  • Intelligent query processing
    • Row mode memory grant feedback
    • New function to count distinct: APPROX_COUNT_DISTINCT.
    • Batch mode on rowstore
      • On operators that uses over about 100 000 rows.
    • Deferred compilation for table variables
  • Java support, through Machine Learning Services (in-database).
  • SQL Graph extentions
    • Can use view and derived table in MATCH.
    • Support for MATCH in MERGE statement.
    • Edge constraints
  • New database scoped settings
  • Availability Groups
    • Up to 5 synchronous replicas (up from 3)
    • Secondary-to-primary connection redirection (re-route the connection to the primary, even if it was made to a secondary).
  • SQL Server audit includes data_sensitivity_information column, if defined using SQL Data Discovery and Classification.
  • Hybrid buffer pool. Database pages on persistent memory will be accessed directly.
  • Support for columnstore statistics in DBCC CLONEDATABASE
  • Support for COLUMNSTORE and COLUMNSTORE_ARCHIVE in sp_estimate_data_compression_savings.
  • Light-weight query profiling support enabled by default. This is what "Live Query Statistics" uses.
  • New polybase connectors for SQL Server, Oracle, Teradata and MongoDB
  • New DMVs
    • Sys.dm_db_page_info, replacement for DBCC PAGE.


SQL Server 2017
https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017
  • SQL Server on Linux OS
  • White-listing of assemblies along with sp_configure option clr strict security
  • New DMV to get statistics for transaction log for a database: sys.dm_db_log_stats().
  • Resumable online index rebuild.
  • IDENTITY_CACHE allowing for non-gaps in identity on hard shutdown (but with a performance penalty).
  • Graph tables
  • Adaptive Query Processing:
    • Adaptive joins.
    • Memory grant feedback.
    • Interleaved execution for MSTVF.
  • Automatic tuning
    • Automaticv plan correction. Identify regressions and force the better plan automatically.
    • On Azure SQL Database we also have "automatic index management", auto create and drop indexes.
  • Significant performance improvements when rebuilding BW-tree non-clustered indexes om memory-optimized tables.
  • Column modified_extent_page_count in sys.dm_db_file_space_usage tracks differential changes in each database file.
  • SELECT INTO has option to specify filegroup.
  • Setup allow for 256 GB tempdb files. Watch out for using a large file size if you don't have Instance File Initialization.
  • Improved backup performance for small databases on servers with lots of memory (avoid multiple sweeps of the BP).
  • Query Store track wait stats summary info.
  • Temporal tables
    • Cascade delete and update for foreign keys.
    • Supported retention policy (keep x number of days/months...)
  • Improvements to indirect checkpoints.
  • Columnstore indexes
    • On-line build and rebuild of non-clustered
    • Clustered supports LOB columns
  • Availability groups:
    • Cross database transactions.
    • Clusterless.
    • Minimum Replica Commit Availability Groups.
    • Linux, including mixed environments.
  • New DMVs
    • sys.dm_db_log_stats exposes summary
    • sys.dm_tran_version_store_space_usage
    • sys.dm_db_log_info. Replacement and enhancement to DBCC LOGINFO.
    • sys.dm_db_stats_histogram
    • sys.dm_os_host_info
  • Database Engine Tuning Advisor allow specify Query Store for workload
  • In-memory
    • Computed columns
    • JSON
    • CROSS APPLY
  • Functions
    • New
      • CONCAT_WS
      • TRANSLATE
      • TRIM
    • WITHIN GROUP for STRING_AGG
  • BULK INSERT and OPENROWSET(BULK...) support CSV.
  • Improvements to the way incremental statistics update thresholds are computed (140 compatibility mode required).
  • Enhanacements to memory-optimized objects
    • Sp_spaceused support
    • Sp_rename
    • CASE
    • Remove limitation for 8 indexes
    • TOP(N) WITH TIES
    • ALTER TABLE faster
    • Transaction log redo now parallell
    • Azure storage support for storing databases and backup
SQL Server 2016
https://docs.microsoft.com/en-us/sql/database-engine/whats-new-in-sql-server-2016
  • Sp1 
    • CREATE OR ALTER for procedures, views, functions and triggers.
    • A bunch of functionality that earlier required Enterprise Edition is now available in lower editions. Examples:
      • Partitioning
      • Data compression
      • Fine grained auditing (database audit specifications)
      • In-memory OLTP
  • Columnstore
    • Broader support for batch mode execution
    • Updateable non-clustered index
    • Non-clustered index can have filter clause
    • Columnstore index for memory-optimized table
    • Support for row-store non-clustered indexes on clustered columnstore table
    • Primary keys and foreign keys on clustered columnstore table
    • Compression delay
    • Support for read-committed snapshot and snapshot isolation
    • Remove deleted rows using ALTER INDEX ... REORGANIZE
    • Access CS indexes on AG replica
  • In-memory OLTP
    • Storage format change. No longer uses filestream code for storage.
    • ALTER TABLE is optimized regarding usage of transaction log. And can also run in parallel.
    • Statistics is update automatically
    • Parallel (index) and heap scan
    • Unique constraints and indexes
    • FK between memory optimized tables
    • CHECK constraints
    • Non-unique index allos NULL values in keu
    • Triggers (AFTER)
    • Full collation support
    • ALTER (add and drop indexex, chage bucket count, add columns etc)
    • Row length can exceed 8060 bytes
    • LOB support
    • Other performance and scalability enhancements
  • No need to opt-in to get optimizer fixes (trace flag 4199 no longer "needed")
  • Compatibility level guarantees for performance (get the old plan with old db compatibility level)
  • Up number of FK's to a table from 253 to 10,000.
  • Parallel update of samples statistics
  • No need to update as many row to get auto update of statistics (changes from 20% to SQRT based formula.
  • INSERT part of INSERT ... SELECT can go parallel
  • Live Query Statistics
  • Query Store
  • Temporal tables
  • Backup
    • Striped backups to Azure Blog storage
    • File-snapshot backups to Azure Blob storage
    • Manages backups
  • Tempdb
    • No need for trace flag 1117 (grow one and all files grow) and 1118 (use uniform extent from the start). These are on by default.
    • Setup default to several data files.
    • Initial file size by default is 8 MB and autogrow is 64 MB. You can change this in setup.
  • JSON support
    • FOR JSON
    • OPENJSON
    • ISJSON
    • JSON_VALUE
    • JSON_QUERY
    • JSON_MODIFY
    • No JSON data type, though
  • Polybase, run queries on Hadoop or Azore Blob Storage from TSQL.
  • Stretch database (to Azure SQL Database)
  • Some tooling get support for UTF-8 (BCP, BULK INSERT and OPENROWSET)
  • New, increased values by default for database file size and autogrow.
  • TRUNCATE TABLE can truncate specified partition.
  • More support for "online" ALTER TABLE operations,
  • NO_PERFORMANCE_SPOOL optimizer hint
  • Increase key size for nonclustered indexes from 900 to 1700 bytes.
  • DROP x IF EXISTS ... (x can be table, index, etc)
  • MAXDOP for DBCC CHECKDB and other DBCC CHECK.
  • Advanced Analytics (R)
  • New and enhanced functions:
    • COMPRESS() and DECOMPRESS()
    • SESSION_CONTEXT()
    • DATEDIFF_BIG() and AT TIME ZONE
    • New properties in SERVERPROPERTY()
    • HASHBYTES(), restriction of 8000 bytes removes
    • STRING_SPLIT()
    • STRING_ESCAPE()
  • Traceflag 1117 is replaced by database file group option AUTOGROW_ALL_FILES. Traceflag is now a dummy. Option is not on by default (except for tempdb).
  • Traceflag 1118 is replaced by MIXED_PAGE_ALLOCATION database setting, which is on y default. Trace flag is now a dummy.
  • Lots of increased support for natively compiled moduled, for example
    • UNION and UNION ALL
    • DISTINCT
    • OUTER JOINS
    • Subqueries
    • We can use native compilation for scalar functions. and inline table functions.
  • Security-related
    • Row-level security
    • Always encrypted
    • Dynamic data masking
  • High Availability enhancements
    • Basic Availability Groups for Standard Edition (basically a replacement for database mirroring)
    • AG: Load-balancing among read-only replicas
    • AG: Increased to three replicas for auto fail over (from two)
    • GSMA for failover cluster
    • AG: MSDTC support between instances
    • Can configure failover if database goes offline
    • Distributed availability group (AG over separate windows cluster)
    • Direct seeding (we don't have to restore database)
    • Performance enhancements

SQL Server 2014

From memory...
  • Standard Edition support 128 GB memory (from 64 GB)
  • In-memory OLTP introduced
    • Memory-optimized tables
    • Natively compiled stored procedures
  • Database files in Azure Blog Storage
  • Managed backups to Azure
  • BAckup encryption
  • New Cardinality Estimator
    • For the first time since 7.0
    • And for every subsequent release (2016, 2017, ...) we get a new release of the CE
  • Delayed Durability
  • AG: From 4 to 8 secondary replicas, and other enhancements
  • Rebuild index and table at partition level
  • Columnstore indexes
    • Clustered (updateable)
    • COLUMNSTORE_ARCHIVE compression in ALTER INDEX REBUILD
  • Buffer Pool Extension
  • Incremental statstics at partition level
  • Resource Governor: I/O
  • Compatibility level 90 (2005) removed
  • In-line syntax to create index (in CREATE TABLE)
  • SELECT INTO can go parallel
  • ReFS support for database files

SQL Server 2012

From memory...
  • Availability Groups
  • FCI 
    • Multi-subnet failover clusters
  • Online Index Rebuld also for (max) data types
  • Contained databases
  • Tuning Advisor can use plan cache
  • File Tables
  • Spatial enhancements
  • SC collations
  • SEQUENCE
    THROW
  • 14 new scalar functions, ex TRY_CONVERT
  • Extended Events as full replacement for SQL Trace (Profiler)
  • Windowing, i.e. OVER, ROWS BETWEEN, RANGE BETWEEN, LAG, LEAD, etc
  • Columnstore index
  • 15000 partitions (from 1000)
  • BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are no longer sysadmins
  • Create your own server roles
  • Auditing, Server-level for all editions
  • Backup to Azure Blob storage
  • Databasfiler on SMB (3.0), UNC
  • Tempdb locally FCI
  • Virtual Service-accounts for services, is default in setup
  • Support for Managed Service Accounta (MSA)

SQL Server 1.1
From memory...
  • UNION