In this article, we will cover all the new feature in SQL Server 2019.
The previous version of SQL 2017 came with support for Linux (Redhat, Ubuntu, Linux Server) and Docker engine, and that was the big step by the SQL Team.
SQL Server 2019
We can select the desired Operating system version to install SQL Server 2019 and now we can build this on Windows, Linux, Docker, and Big Data Analytics containers with Kubernetes images as well.
This version of SQL Server was mainly developed to put the focus on scalability, intelligence, performance, security, and also the key point is data exchange between distributed file systems like Hadoop, Apache spark, connecting with Big Data.
Error Messages
SQL 2019 has introduced ‘VERBOSE_TRUNCATION_WARNINGS’ attribute and it can be set to ON and OFF
If we set it to ON for any data truncation error the message is overridden and the new advanced message with error code (2628). Below is the message,
‘String or binary data would be truncated in table ‘Table Name’, column ‘Column name’.
Truncated value: ‘Value’.
Here will get the detailed error message with the Table name, Column name, and Truncated value as well
We set the attribute flag is OFF
Regular error message:
‘String or binary data would be truncated.
Vulnerability Assessment
We can use this assessment to track compliance of SQL Server instances and Azure SQL Database instances with recognized security best practices and also we can implement using the reports shared by this tool. This provides easy to implement security compliance such as GDPR.
UTF-8 Support
It supports UTF-8 Character encoding. We can now create a char or varchar column to store UTF-8 data. This feature improves data compatibility and performance improvements.
Suppose I have a table with the collation LATIN1_GENERAL_100_CI_AS_SC. Here AS means, Accent-Sensitive data can be stored in this table and the above can be changed to LATIN1_GENERAL_100_CI_AS_SC_UTF8
Enhancement in SQL Graph
It provides enhancements to include match support with T-SQL MERGE and edge constraints. We can now limit the type of nodes a given edge type can connect. This helps users to enforce restrictions on the edge table. It also maintains data integrity in their graph database. It also supports MATCH predicates to specify graph relationships in a single statement, instead of separate DML(Data Manipulation Language) statements. This makes it easy to query the graph database.
Online Index CREATE Operations
SQL Server 2019 has allowed the user to create, rebuild, alter or drop a clustered index while the database or underlying table is busy in operation, this means one user can rebuild the index while at the same time, the other can fire an updated query on the same database or database table. In earlier versions of SQL Server, we needed to rebuild the indexes in offline mode and additionally, it will block the whole table,
Syntax
ALTERINDEX index_name
ON table_name
sUREBUILD WITH (ONLINE =ON);Supported Features
SQL 2019 extends its maximum number of simultaneous replicas to 5, in an earlier version of SQL -2017 it was 3 and therefore it consists of primary and secondary replicas as well.
The client applications can be connected to the primary replica irrespective of the connection string.
Certificate Management Functionality in SQL Server Configuration Manager:
In SQL Server 2019, certificate management is integrated into the SQL Server Configuration Manager. We can view and validate the certificates being used in the SQL Server instance. We can view and validate certificates installed in a SQL Server instance. This also provides detail about certification expiration dates. This feature helps to manage certificates in a better way. We can also deploy certificates for AG instances starting from the primary replica.
Big Data Clusters
Data virtualization — Combine data from many sources without data movement. It improves security and performance to process data.
Managed SQL Server, Spark, and Data Lake — Store high volume data in a data lake and access it easily using either SQL or Spark. It is very easy to manage using management services, admin portal, and integrated security.
Complete AI platform — Easily feed integrated data and prepare the solution. This solution works to operate all models in one system.
Data Classification
It is also a nice feature of SQL 2019, in which we can classify the data according to the business department or subject like medical data, health data, finance data, etc. It will bring these easy to apply different security standards according to different data sets. In short, if we identify and classify data separately, then it is easy to apply different security policies according to the nature of the data.
Source: Medium; Code X
The Tech Platform