Best Additions to MSSQL

(since 2005)

1. High Availability & Disaster Recovery

• Always On Availability Groups (2012): Replaced database mirroring, allowing admins to group multiple DBs and fail them over together to multiple readable secondary replicas.  
• Contained Databases (2012): Logins are contained in the DB. The old way was a Master system DB with Logins and a specific DB with its Users, you lose the Logins that go with the Users. This broke on backup and restores of the DB from DEV to QA or to PROD.  
• Link for Azure SQL Managed Instance (2022): Introduced near real-time, bi-directional disaster recovery linking on-premises servers directly to the Azure cloud.

2. Quantum Leaps in Performance

• Columnstore Indexes (2012 / 2016): Built highly compressed, column-based data storage natively tailored for data warehousing, accelerating analytical query speeds by 10x to 100x.
• In-Memory OLTP / "Hekaton" (2014): Added memory-optimized tables and natively compiled SPs, removing lock and latch contentions to achieve up to 30x throughput improvements. The memory-optimized tables use pointers in RAM rather than data pages. By removing locks in this system the first committer wins with the other transaction incurring a rollback.  However, this assumes the developers have done a bunch of TRY / CATCH blocks in C# code. Good for logging millions of rapid telemetry streams, IoT sensor packets, or high-volume financial ticks without bottlenecking the system.
• Accelerated Database Recovery (2019): Allows massive transaction roll backs instantaneously regardless of concurrent workload sizes.

3. Intelligent & Automated Query Tuning

• Query Store (2016): Captures the execution plan history and allowing DBAs to instantly force better-performing query plans. Improved later by Auto Plan Correction (2017), Custom Capture Policies (2019), and Enabled by Default (2022) (for new DBs). The Query Store data travels seamlessly with your database backups and restores because it lives in the MDF file.
• Intelligent Query Processing (2017–2022): Built automation directly into the optimization engine. The DB dynamically fixes parameter sniffing issues, adjusts memory grants, and inlines scalar user-defined functions (UDFs) on the fly without changing code. 

4. Enterprise Security Modernization

• Transparent Data Encryption (2008): TDE introduced full encryption for data at rest, shielding physical DB files (.mdf/.ldf) and system backups from unauthorized access to project metadata, execution logs, and parameters stored inside the catalog. Disadvantages are fat backups, since encryption does not compress down well.
• Always Encrypted (2016): Enabled client-side encryption where sensitive data is protected both at rest and in transit, ensuring even database administrators cannot view plaintext data.
• Row-Level Security & Dynamic Data Masking (2016): Added granular, built-in access controls to hide or mask sensitive data programmatically based on the executing user's privileges.
• Ledger for SQL Server (2022): Brought tamper-evident, blockchain-style capabilities to standard tables to cryptographically prove data integrity for compliance auditing. 

5. Rigid Platform Breakout

• Works In Linux (Red Hat, Ubuntu, SUSE) and Docker Containers (2017): Severed the dependency on Windows Server, allowing native deployment to other places.
• PolyBase & Big Data Clusters (2016 / 2019): You can use T-SQL to query Hadoop, Cosmos DB, and S3-compatible object storage.
• Native JSON & Graph Support (2016 / 2017): Can parse, format, and structure JSON documents and complex, interconnected graph data natively.

6. Schema/Table Changes 

• Temporal tables (2016): Niche, but useful.  Provided native, built-in tracking of data changes over time, allowing users to query data exactly as it existed at any specific point in the past. Temporal tables have a main table and a history table. You use FOR SYSTEM_TIME to query the data at some point in time. Great for data auditing & forensics, table with historical rows (such as items with prices that change due to inflation or customer Sarah changing her city and thus affecting the financials by city and region), and "oopsies".