Top 10 New Features in SQL Server 2019

Certainly! SQL Server 2019 comes with a host of new features that can make your database management more efficient and effective. In this blog, we’ll take a look at the top 10 new features in SQL Server 2019 and how you can use them.

Big Data Clusters

Big Data Clusters is a new feature in SQL Server 2019 that allows you to work with large amounts of data from multiple sources. It combines SQL Server with Apache Spark and Hadoop Distributed File System (HDFS) to provide a unified data platform. With Big Data Clusters, you can easily process and analyze big data using familiar T-SQL commands.

To use Big Data Clusters, you’ll need to install the feature and set up a Kubernetes cluster to manage the SQL Server instances and other big data services.

Intelligent Query Processing

Intelligent Query Processing (IQP) is a set of features in SQL Server 2019 that improve query performance by optimizing the execution plan. It includes features like batch mode on rowstore, memory grant feedback, and approximate query processing.

To use IQP, you don’t need to make any changes to your code. Simply enable it on your database or instance and SQL Server will automatically use the new features when executing queries.

Accelerated Database Recovery

Accelerated Database Recovery (ADR) is a new feature that improves database availability and reduces downtime caused by long-running transactions. It does this by automatically rolling back transactions that are blocked by other transactions or system processes.

To use ADR, you need to enable it on your database. Once enabled, SQL Server will automatically use ADR to recover from failures and ensure that your database is always available.

UTF-8 Support

UTF-8 is a popular character encoding standard that supports a wide range of characters and symbols. SQL Server 2019 now supports UTF-8 encoding, making it easier to store and retrieve data in multiple languages and character sets.

To use UTF-8 encoding, you need to specify the encoding when creating or modifying a database or table. You can also use the new UTF-8 collations to sort and compare data.

Resumable Index Creation

Resumable Index Creation is a new feature that allows you to pause and resume index creation without losing progress. This can be useful when creating large indexes that take a long time to complete.

To use Resumable Index Creation, you need to specify the RESUMABLE option when creating or rebuilding an index. You can then pause and resume the operation as needed.

Java Language Extension

SQL Server 2019 now supports the Java language, allowing you to write stored procedures and functions using Java. This can be useful if you have existing Java code that you want to integrate with your SQL Server database.

To use the Java language extension, you need to install the feature and set up a Java Development Kit (JDK) on your server. You can then create and execute Java-based stored procedures and functions using T-SQL.

Improved PolyBase

PolyBase is a feature in SQL Server that allows you to query external data sources like Hadoop, Oracle, and Teradata from within SQL Server. SQL Server 2019 includes several improvements to PolyBase, including support for more data sources, improved performance, and better security.

To use PolyBase, you need to configure the feature and set up the necessary connections to your external data sources. You can then use T-SQL to query the external data as if it were a regular SQL Server table.

In-Memory Database Enhancements

In-Memory OLTP is a feature in SQL Server that allows you to store and process data in memory for faster performance. SQL Server 2019 includes several enhancements to In-Memory OLTP,

including support for persistent memory and a new feature called memory-optimized TempDB metadata.

Persistent memory is a new type of memory that sits between RAM and solid-state drives (SSDs). With SQL Server 2019, you can use persistent memory to store your In-Memory OLTP data, providing faster access and better durability.

Memory-optimized TempDB metadata is a feature that allows you to store the metadata for temporary objects like tables and indexes in memory. This can improve the performance of queries that use temporary objects.

To use these In-Memory database enhancements, you need to enable the In-Memory OLTP feature and configure your database for memory-optimized tables and indexes

Data Discovery and Classification

Data Discovery and Classification is a new feature in SQL Server 2019 that helps you identify sensitive data in your database and classify it based on the level of sensitivity. This can be useful for compliance with data protection regulations like GDPR and CCPA.

To use Data Discovery and Classification, you need to enable the feature and configure it for your database. You can then use the Classification tab in SQL Server Management Studio to view and manage your data classifications.

Improved Security

SQL Server 2019 includes several improvements to security, including support for Always Encrypted with secure enclaves, improved auditing, and enhanced threat detection.

Always Encrypted with secure enclaves is a feature that allows you to encrypt sensitive data in your database using a secure enclave. This provides additional protection against attacks like SQL injection and man-in-the-middle attacks.

Improved auditing allows you to capture more detailed information about database activity, including SQL statements, database changes, and user activity. Enhanced threat detection uses machine learning to identify suspicious activity in your database and alert you to potential security threats.

To use these security features, you need to configure them for your database and enable the necessary settings in SQL Server Management Studio.

Conculsion

SQL Server 2019 includes a wide range of new features that can help you improve the performance, reliability, and security of your database. To get started with these features, you need to install the necessary components and configure your database accordingly. With these tools at your disposal, you can unlock the full potential of your SQL Server database and take your data management to the next level.

Thanks for reading the article. Let me know if you have any comments or feedback in the comment section or mail me @ [email protected].