This post is part of a series we are calling “SQL Server 2016 – The Final Countdown.”
SQL Server 2016 was the version where Microsoft really started giving us the good stuff for free. Query Store, Columnstore for everyone, Always Encrypted, Availability Groups that worked, and more. Plus, the Service Pack 1 feature additions made Standard Edition folks feel like they weren’t second-class citizens anymore.
But that was a decade ago, and as of July 14th, 2026, SQL Server 2016 is out of support. No more updates or hotfixes, so it’s well past the time to upgrade to a newer version. I know upgrades can seem painful, and we can help with that, but on the other side of that upgrade are many features added since SQL Server 2016 that could make your life easier.
Let’s walk through some of my favorite new features from each version, in the style of an executive summary in case you want to copy/paste to your supervisors to convince them to upgrade. I’ll keep the descriptions short, tell you why you should care, and link to pages with more info should you want to learn more.
SQL Server 2017
SQL Server 2017 shipped only 16 months after 2016, which by Microsoft’s historical standards is practically a patch. Still, they managed to slip in some real gems.
SQL Server on Linux
We were all assured for decades would be an icy day in a place hotter than the desert here in Arizona before Microsoft ever supported Linux, but starting with 2017 you can run the database engine on Red Hat, Ubuntu, SUSE, or in a Docker container. If you work somewhere that has a Linux-first policy, SQL Server is no longer a non-starter, and if you’re a DBA, containerized SQL Server means you can spin up a test instance in roughly the time it takes to refill your coffee. Read more from Microsoft.
New String Functions
Developers, rejoice! STRING_AGG, TRIM, TRANSLATE, and CONCAT_WS may seem like cute new functions, but STRING_AGG alone eliminates the XML PATH abomination we’ve been using since SQL Server 2005. If you’ve never written FOR XML PATH(”) followed by STUFF to concatenate rows, count yourself lucky because now you’ll never have to. Read more from Aaron Bertrand.
SQL Server 2019
This version has been very stable for years, and I would guess as of this writing it is the most popular version with our clients. And it had a few neat feature additions too (and no, I’m not talking about the troublesome scalar UDF inlining and all its requirements.)
Accelerated Database Recovery (ADR)
Before ADR, if a long-running transaction got killed or the server crashed mid-flight, SQL Server had to chew through every log record from the oldest active transaction before the database came back online. For a well-behaved OLTP system that’s fine, but for a system where somebody just ran a three-hour ‘UPDATE’ with no ‘WHERE’ clause, recovery could take hours. That’s real pain.
ADR uses a persisted version store (similar to RCSI) so rollbacks and recovery are nearly instantaneous regardless of transaction size. Crash recovery can go from minutes to seconds. Read more from Microsoft.
Memory-Optimized Tempdb Metadata
If you’ve ever worked on a busy OLTP system where everybody and their uncle is creating and dropping #temp tables, you’ve seen the PAGELATCH_* waits on the tempdb system tables. It’s a classic contention point that no amount of “add more tempdb files” will ever fully solve.
SQL Server 2019 lets you shove the tempdb system metadata tables into memory-optimized (Hekaton) tables, eliminating that latch contention entirely. Doing this requires a server-level setting and a restart, but for the right workload it’s night and day. Note, this feature seemed to have gotten some big improvements in SQL Server 2022. (Watch Haripriya Naidu’s video with more)
SQL Server 2022
The marketing pitch for 2022 was “the most Azure-connected SQL Server ever!” which is great if you’re in Azure and slightly less exciting if you’re not. But even setting aside the cloud stuff, there are some cool features here.
Query Store Is On By Default (Finally)
New databases created in 2022 have Query Store turned on out of the box. It took them six years since the 2016 release, but hey, these things take time. Query Store is also now supported on readable secondary replicas in Availability Groups, which means your read-only reporting workload is finally observable. Read more from Microsoft.
Query Store Hints
You can now apply query hints *without changing application code*. Specifically, you use sys.sp_query_store_set_hints to attach hints like RECOMPILE to a specific query_id in the Query Store. For anyone who’s ever had to performance tune a vendor-supplied application where you can’t change the SQL, this is a gift. Read more from Microsoft.
S3-Compatible Object Storage for Backup/Restore
You can now BACKUP TO URL and RESTORE FROM URL against S3-compatible object storage, and not just Amazon S3 but also Dell, HPE, Pure Storage, NetApp, MinIO, and others. For anyone running SQL Server in a hybrid shop, this is a much simpler backup target than wrestling with file shares or Azure-only URLs. Read more from Microsoft.
Contained Availability Groups
In every pre-2022 Availability Group (AG), you had to manually keep logins, SQL Agent jobs, permissions, and linked servers in sync across replicas. (Did you ever forget to add logins on the secondary, fail over, and hear the lamentations of the users?) With contained AGs, users/logins/permissions/jobs live inside the AG itself using contained master and msdb databases so across replicas these consistencies are handled for you. Read more from Microsoft.
SQL Server 2025
SQL Server 2025 went General Availability (GA) on November 18, 2025. This is the first release in a long time where there are several situations to push clients to upgrade faster rather than slower.
Standard Edition Finally Gets Some Love
For the first time since 2016 SP1, Standard Edition got a real upgrade, including:
- 32 cores (up from 24)
- 256 GB RAM (up from 128 GB)
- Resource Governor is now included in Standard (it was Enterprise-only forever)
- There’s a new Standard Developer Edition so you can build dev/test environments that actually match your Standard production limits
If you’re a shop that has been running Standard Edition for years and feeling the squeeze, this alone is a reason to skip 2022 and jump straight to 2025. More from Microsoft.
(Related: Express Edition gets some more love too, which now supports 50 GB databases, up from 10 GB.)
Native Vector Type and Vector Search
Yes, this is the “AI” feature, and yes, it’s real. SQL Server 2025 has a native VECTOR data type, a vector index powered by DiskANN, and functions like VECTOR_DISTANCE, VECTOR_SEARCH, and AI_GENERATE_EMBEDDINGS that let you call out to an embedding model from T-SQL. Whether or not you personally need to build a RAG chatbot, the broader implication is big: your database can now do semantic (meaning-based) searches, not just keyword matching. Read more from Andy Yun.
Optimized Locking
Optimized Locking uses Transaction ID (TID) locking and Lock After Qualification (LAQ) to dramatically reduce lock memory use and lock escalation. For high-concurrency OLTP workloads, Microsoft has published telemetry showing 80% of locks being skipped in some cases. It requires ADR to be enabled, but no application changes. Read more from Microsoft.
Native JSON Type + JSON Indexes
Since 2016 we’ve stored JSON as NVARCHAR(MAX) and lived with it. 2025 introduces a real native JSON data type (binary-stored, up to 2 GB per document) and lets you create JSON indexes on specific path expressions. There are still some quirks with JSON_VALUE not always using the index, so test carefully. Read more from Microsoft.
But wait, there’s more
Just so you know, that was not a comprehensive list. There have been many new features added since SQL Server 2016 that, depending on your situation, could benefit your organization. These other features are all included in SQL Server 2025:
- Fabric Mirroring if you have Microsoft Fabric reasons.
- Graph Database Support for those modeling hierarchies or social networks.
- Ledger to give you a tamper-evident (cryptographically verifiable) record of every change made to specific tables.
- Optional Parameter Plan Optimization (OPPO) can now generate different execution plans adapted to the runtime parameter values for “kitchen sink” queries.
- Regex Support with a bunch of new T-SQL commands.
- TDS 8.0 / TLS 1.3 support for the security-minded folks.
- ZSTD Compression allows for faster, small backups than the default compression.
Thanks for reading. Now go forth and plan to upgrade your SQL Server 2016 instances.