Sql Server 2019 New Features

Estimated study time: 7 minutes. A release focused on performance and big data integration.

SQL Server 2019 focused heavily on performance improvements that work "out of the box," along with expanding SQL Server's reach into big data workloads.

Intelligent Query Processing (IQP)

This umbrella of features improved query performance automatically, without requiring code changes. Highlights include:

• Batch Mode on Rowstore: brings batch-mode execution benefits to traditional rowstore tables, not just columnstore.
• Table Variable Deferred Compilation: fixes long-standing cardinality estimation problems with table variables.
• Adaptive Joins & Memory Grant Feedback: let the engine adjust its plan based on actual row counts at runtime.

Big Data Clusters

SQL Server 2019 introduced Big Data Clusters, letting you deploy scalable clusters of SQL Server, Spark, and HDFS containers together on Kubernetes — combining relational and big data processing in one platform.

UTF-8 Support

Native UTF-8 collation support was added for char and varchar data, reducing storage compared to nvarchar for multilingual text:

CREATE TABLE Messages (
  Body VARCHAR(500) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

Always Encrypted with Secure Enclaves

This extended Always Encrypted to allow rich computations (like pattern matching and range comparisons) on encrypted data inside a secure enclave, without ever exposing the plaintext to SQL Server itself.

Accelerated Database Recovery (ADR)

ADR redesigned the recovery process so long-running transaction rollbacks complete almost instantly, avoiding the old problem of a single runaway transaction blocking recovery for everyone else.