Background
The first version of Microsoft SQL server database 1.0 was developed in 1989 and it does extend till date, with each improved versions, more flexibility and usability are introduced in it. SQL 2017 was released in the year 2017 with supports to Linux (Redhat, Ubuntu, Linux server) and Docker engine, that was a big step by the SQL team. From SQL server 2016, SQL only supports 64 bit processors, Now they have again moved ahead with this current version.
(**See here for What's new in SQL 2017.)
Introduction
Now, SQL has released its new version (15.0) on Nov 2019, This version of SQL Server was mainly developed to put focus on, data exchange between distributed file system like Hadoop, Apache spark, connecting with big data, improved security, better performance, scalability, intelligence. You can install SQL 2019 on Windows, Linux or on Docker environment. Let's walkthrough its innovative and compliance features.
Pro-Developer
This build has new and good features for developers, like improvement in graphs, UTF-8 support, changes in spatial datatypes, a new framework that permits developer to use any language of their choice to retrieve and gain access of data, let's trace them.
Graph Enhancement
- If you have ever dealt with the graph database (graph database concept is introduced in SQL 2017, you can check it out here), then you must know about edge constraint which is nothing but used to keep the data integrity on the graph database edge tables. There are changes in edge constraints concept in SQL 2019, Now we can define cascade delete actions on edge constraints.
- Tables and index partitioning concepts are now supported by graphs tables also.
- Graph database has introduced a new function, to find the quickest path between given two nodes in graph, this may be useful for node traversals, the new function name is
SHORTEST_PATH and which is used with MATCH function in SELECT statement.
UTF-8 support
Now a days, database needs to be more supportive in terms of different languages, we may consider it as a requirement of the real world. In this version of SQL, database now supports UTF-8 character encoding, Now it has good support for import and export encoding to database, table or column level collation Polybase and Always Encrypted like functionalities are also covered here. UTF-8 is allowed in char and varchar datatypes, so in collation, you can now use UTF-8, see the below sample, it will clear your doubts.
Suppose I have a table with collation LATIN1_GENERAL_100_CI_AS_SC. Here, AS means Accent-sensitive data can be stored in this table (Accent-sensitive means support to accented characters like support to 'a' and 'αΊ₯ '. above collation can be changed to LATIN1_GENERAL_100_CI_AS_SC_UTF8.
Error Messages
When we load data from one table to another or one database to another, then there might be chances of failure due to datatype or data size limit mismatch and to track this error may be time consuming if there is large volume of data that needs to be transferred. In this case, SQL 2019 has introduced 'VERBOSE_TRUNCATION_WARNINGS' attribute, it can be set to ON or OFF. If we set it to ON for data truncation error, our traditional message is overridden with new advanced descriptive message with the error code (2628). The error message reads as below:
'String or binary data would be truncated in table 'BLAH', column 'BLAH'.
Truncated value: 'BLAH'.
Here you will get details in error message with table name, column name and truncated value too, I think it would be one of the recommend developer features of SQL.
If we set the attribute flag as OFF, then we will get regular legacy message as below:
'String or binary data would be truncated.
Language Support
SQL server 2019 now has support for Java language SDK, basically it's an Extensibility support for Java SDK which is open source. Different Java datatypes are also supported in this version of SQL, like float, java.sql.date, java.math, java..sql. timestamp.
SQL server also comes with Language Extension pack, which gives you a way to execute external code, here execution of the code is carried out isolated, so that core engine should not get affected with this operation. This extension ensures the security, as code gets executed where the data resides so it will disregard the process of fetching data outside the network and enforce the security constraint.
Spatial Reference Identifiers
Do you know spatial datatype? The data of this datatypes are used to store information about physical locations and geomatic objects, these objects can be road, river, road or tree (anything in world). In SQL 2019, there is change in spatial reference systems, basically there are two new spatial references that are added: 7843 for geographic 2D, 7844 for geographic 3D which is according to Australian GDA2020 [Ref: MSDN].
Support to Big Data
SQL server 2019 has introduced Big Data clusters which will provide a good support to work with Big Data, large data sets and AI.
With SQL 2019, you can now process, transact big data using spark.
It can easily connect big data with relational data and can query on external data sources.
SQL users HDFS containers to manage and store BigData.
Accelerated Database Recovery [ADR]
SQL server 2019 comes up with a quickest database recovery feature, if there are some long running transactions or database failure and if the ADR (Accelerated database recovery) of that database is switched ON, then it will rollback the process with lighting speed, it also helps to cancel the long running-hanged up database processes.
The syntax would be as below:
ALTER DATABASE <db_name> SET ACCELERATED_DATABASE_RECOVERY = ON;
In the above syntax, db_name is the name of the database on which we have to set accelerated database recovery to ON.
Online Operations
SQL server 2019 has allowed user to create, re-built, alter or drop clustered index while the database or underlying table is busy in operation, means one user can rebuild the index while at the same time, the other can fire update query on the same database or database table (isn't it a great feature), in earlier versions of SQL server, we need to rebuild the indexes in offline mode, and additionally, it will hold the exclusive lock on database and its table with data and this will block the whole table, see syntax below:
ALTER INDEX index_name
ON table_name
REBUILD WITH (ONLINE = ON);
In the above syntax, index_name is the name of the index that we need to create, alter or drop with the flag as ONLINE is ON.
Always Available
The first business need of the database server is, be always available anytime. To run business seamlessly, the database needs to be always available and never goes down, to maintain this, we should have a database replica that is always available when the main database failure occurs. To consider this crucial requirement, SQL 2019 extends its maximum number of simultaneous replicas to 5, in earlier version of SQL (2017), it was 3. Here, one of the replicas is primary replica and the other are secondary replicas.
There is one more additional feature introduced in SQL 2019, client applications can be connected to the primary replica irrespective of the connection string.
Improved Security
From the past many years, SQL is dealing with the security aspect and in each version, it comes up with more secure and stable infrastructure. In SQL 2019 also, some of the security features improved like Always encrypted, Certificate management, Data discovery and Auditing. Let's trace them.
Always Encrypted With Secure Enclave
Always encrypted feature is already published by SQL in earlier version (2017), in this version, Always encrypted comes with secure enclaves. We know that always encrypted functionality used to provide security to data by encrypting it. But there is some limitation to this functionality such as:
- As the data remain encrypted inside SQL server too, it is hard to perform other operation on encrypted data (equality comparisons is the only operation allowed on encrypted data).
- To perform other operation on this encrypted data, a user needs to move the data out of database and then decrypt it.
To overcome these limitations, secure enclave is introduced, in which data is converted to plain text in secure enclave and all other operations will be possible. Secure enclave is like a safe place for SQL to treat sensitive data.
To perform other operation on data, we need to fetch them outside SQL and decrypt it.
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 the ease to apply different security standard 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 nature of data.
ADD SENSITIVITY CLASSIFICATION is the attribute used to assign data classification to different columns, see below sample:
ADD SENSITIVITY CLASSIFICATION TO
dbo.column1, dbo.column2
WITH ( LABEL='Confidential Data', INFORMATION_TYPE='Business' )
In the above snippet:
Column1 and Column2 are the columns on which we have applied data classification label and information type.
Certificate Management
SSL/TSL certificates can be used in SQL to maintain and enhance the security but in earlier version, we need to run some scripts and manual commands to use these certificates but in this current version, certificate management is part of SQL configuration management and it gives you ease to install certificates.
Performance
Each version of SQL comes with some performance increment tips and tricks but this version of SQL has added intelligence in performance and execution means, it will gives you predictable performance steps for your bottlenecks and slow transactions. SQL 2019 has introduced some features related to intelligent performance like query force plan, reduce recompilation, sequential key optimization. Let's trace them.
Query Force Plan
In this feature, Query store now has the ability to drive query execution plan for static and fast-forward querys and cursors, and this can happen with the help of sp_query_store_force_plan or using query store reports (exist in SQL Server Management Studio).
Reduce Recompilation
SQL server has introduced a new feature of reducing the recompilation of workloads using temp tables across multiple scopes, in earlier version of SQL when execute some DML statement on temp tables and if the table is created outside of the scope, then DML statements get re-compiled each time, with this feature, there is no need to re-compile the SQL statements even if the temp table is created out of scope.
Sequential Key Optimization
If your application has high concurrency and if you have indexing in your insert query, then this feature will be a good turn for you. This feature helps SQL engine to improve accuracy while inserting primary key or date time data when there are high concurrency cases.
In Memory Database Support
SQL 2019 builds and supported by several in-memory databases, that supports scalability and performance of database workloads. Also, it has introduced OLTP for better execution (OLTP: online transaction processing), SQL server has released below features, as a part of in memory database support.
Hybrid Buffer Pool
This feature is intended to access persistent memory and take out data pages files from database (which is stored on persistent memory). This can be done by SQL server database engine. In earlier version, SQL engine does store the data on buffer pool, with this feature SQL server does not copy the data on buffer pool, but accesses a datapage directly from persistent device.
OLTP Support to DB Snapshot
In this version, SQL engine has given online transaction support to database snapshot, a database snapshot is nothing but a static and readonly view of database, this snapshot is coherent with the database transactions at the moment when snapshots is created. The benefits of the snap is, one can query it from producing different data, that data further can be used for MIS or reports, the snapshots are readonly, there is no fear of data loss.
Support to TempDB
An issue of bottleneck and scalability is always up when TempDB has workloads. To overcome this issue, SQL comes up with memory optimized TempDB metadata, which helps to remove bottleneck and improve scalability, basically system tables are used here to manage this operation where temporary tables metadata moved to memory optimized tables.
Wide Platform Support
From SQL 2017, it does support Linux platform, now it has improved a lot on the below features:
- Replication feature on Linux system
- Improved support for MSDTC (Microsoft Distributed Transaction Coordinator)
- Third party AD support
- Polybase non-Hadoop support
- Change Data Capture(CDC) is now supporting SQL in linux
Supervising
We need supervision on each thing operation and functions in SQL. If there are any issues or lack of performance observed, then we need to move to SQL monitoring technique, SQL 2019 has introduced some additional features that are improvement in monitoring.
sys.dm_os_wait_stats
A new wait is introduced in SQL 2019 which will helps us to return information of all wait that are executed operation. You can use this report for performance diagnosis.
lightweight_query_profiling
This attribute helps us in monitoring SQL queries in which it provides profiling infrastructure which will take care of how work load is executing and how resources are being pushed. So this is nothing but a performance measurement technique.
Custom Capture Policy
This policy is introduced in query store, basically this is the query store policy settings and used to fine tune data collection for a specific server. We can enable or disable this policy. We can use this policy with ALTER DATABASE SET option.
dm_exec_query_plan_stats
This is a new function of Transact SQL that used to return last known execution plan for last cached query plan.
Intelligent Query Processing
This feature is introduced in SQL 2019. Intelligent query processing works when critical database work loads are running parallel, IQP (Intelligent query processing) reduces the risk and improves the performance of workload with less impact on existing system, following features are introduced to support IQP.
Row Mode Memory Grant Feedback
This feature is to adjust the memory and grant size to row and batch mode operator, This feature can be set to ON or OFF, this is used with ALTER DATABASE statement, see syntax below:
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
Scaler UDF
Scalar UDF is nothing but a multi-statement scalar function. This function takes only one value as input, and produces aggregated value as output. SQL has introduced in-linging feature on this scalar UDF, which will convert all function in relation expression, and later put them calling SQL query, this will lead to performance improvement of workload, but to use this feature, we need to use database compatibility level 150.
Table Variable Deferred Compilation
If you have queries referencing table variables, then this feature will help to improve performance and plan quality. If the query has referencing a table variable, then compilation process is differed until first execution, basically in this process, it will take actual table variable row count and then this count use further for downstream plan operation.
Approx Query Processing
This is one of the interesting functions where it returns an approx value than a precise value, take an example, if I have billions of geographical data in my table and I want to get the COUNT of DISTINCT rows, then here to process the query faster, APPROX_COUNT_DISTINCT will work faster and return the approx result because the response is the crucial thing here, this function will return non-null value.
Memory Setup Options
In SQL server 2019, now we can:
Minimum Memory
Set minimum server memory (which is nothing but a lower memory limit of SQL engine) it should be lower than 0. This memory will be used by buffer pool and cache memory.
Max Memory
Set maximum server memory (which is nothing but a max memory limit of SQL engine) , the deault value is 2048 TB.
This is the part of Memory page of Database engine configuration.
New Feature in SQL Machine Learning
Following features are introduced in SQL machine learning services:
- To ensure high availability of Machine learning services, we can configure failover cluster in Windows Server.
- You can now execute external scripts per partition using new parameter as '
sp_execute_external_script'.
New Feature in SSRS (Reporting Services)
Following features are introduced in SQL reporting services:
- Connection with power BI
dataset is now possible with report builder or data set tools. - Tooltips can be used to indicate text for each element of the report, it is also known as
AltText feature. - If you are managing security of your Azure web application proxy, then from now onwards, you can use Azure Active Directory Proxy support feature that will allow secure access to your application.
- You can use RegeX pattern to your URL and can customize your headers.
- New report is released, which is compatible with earlier version SQL like 2016, 2017.
This article is just an overview of what we have in SQL 2019, each points can be elaborated more in details. In the next version of this article, we may try to cover it, till then, you can enjoy this article.
Queries and suggestions are always welcome!
Thanks for reading!
History
- 14th January, 2020: Initial version
