Greetings to all visitors to the site Info-Comp.ru! Today we’ll talk with you about what’s new in Microsoft SQL Server 2019, in other words, we’ll find out what new features and functions have been added in this version.
Overview of Microsoft SQL Server 2019
Microsoft SQL Server 2019 is the newest and most current version of the system at the current time.
Microsoft SQL Server 2019 is released in 4 editions:
- Enterprise – the most complete release, includes all the features of SQL Server 2019, designed for large databases that require maximum performance, reliability, scalability and availability, and also have very strict business intelligence requirements;
- Standard – the most common edition, includes key features for data management and business intelligence. In contrast to the Enterprise release, Standard has limitations both in functionality and in the amount of resource use, for example, the maximum number of cores that can be used is 24;
- Developer is a editorial for software developers that includes the full functionality of SQL Server; it allows you to create and test applications based on SQL Server without restrictions. It is free, but only programmers can use it to develop and demonstrate applications, in other words, it cannot be used as a database server in an enterprise;
- Express is a free edition of SQL Server. It is suitable for training and developing data processing applications on desktop computers and small servers ( up to 10 GB in size ). This release also has limitations, for example, you can use only 4 cores, and the maximum possible database size is 10 GB. In terms of functionality, this edition is also significantly inferior to paid options.
The note! You can find many articles on learning the T-SQL language and Microsoft SQL Server in a collection of articles specially created by me for studying Microsoft SQL Server .
New features in Microsoft SQL Server 2019
Now let’s look at the main innovations of Microsoft SQL Server 2019:
- Big data clusters – they help in the implementation of an environment for working with large data sets, including using machine learning and the capabilities of artificial intelligence;
- Batch mode for rowstore data – this mode provides batch mode execution without the need for columnstore indexes. In this mode, processor resources are used more efficiently during analytical queries, therefore it is better to use it when a significant part of the work consists of analytical queries, or if the workload is highly dependent on the processor. Previously, this feature was used, but only if the query included columnstore index operations. However, applications could use functions that do not support columnstore indexes, and therefore could not work in batch mode. Starting with this version, batch mode is available for queries that include operations with both rowstore and columnstore indices;
- Embedding scalar user-defined functions – this feature automatically converts user-defined scalar functions into relational expressions in a SQL query, which significantly improves the performance of queries that use scalar user-defined functions;
- Delayed compilation of table variables – the ability to optimize the plan and improve the performance of queries that use table variables . This is achieved due to the fact that during optimization and initial compilation of instructions, this feature distributes multiplicity estimates based on the actual number of rows of the table variable;
- Approximate query processing using the APPROX_COUNT_DISTINCT function – this function performs COUNT (DISTINCT ()) statistical calculation, however it is optimized for large data sets, it uses less resources and provides better concurrency. It can be used in cases where absolute accuracy is not required, but there are strict requirements for runtime;
- Hybrid buffer pool – this feature provides ( if necessary ) direct access to data pages in database files stored on devices with read-only memory (PMEM);
- Memory-optimized TempDB metadata – This feature provides a new level of scalability for workloads that actively use TempDB. Thus, the system tables associated with managing the metadata of temporary tables can now be moved to tables optimized for working with memory without short-term locking;
- In- Memory OLTP support for database snapshots – SQL Server now supports creating database snapshots that include memory-optimized filegroups;
- OPTIMIZE_ FOR_ SEQUENTIAL_ KEY parameter for indexes – it optimizes the kernel to increase the throughput of inserting operations into the index with a high degree of parallelism. This parameter is for serial key indexes;
- Reduced recompilation of instructions with temporary tables – starting with this version, SQL Server performs additional checks of instructions with temporary tables to avoid unnecessary recompilations, which improves performance;
- PFS Concurrent Updates – “Free Space on Page” PFS is special pages in a database file that allow SQL Server to find free space when allocating space for an object. This improvement allows you to change the way concurrency control is performed using PFS updates so that you can use a general short-term lock rather than an exclusive lock. This behavior is enabled by default in all databases, including TempDB, starting with the 2019 version of SQL Server;
- WAIT_ ON_ SYNC_ STATISTICS_ REFRESH is a new type of wait in the sys.dm_os_wait_stats dynamic system view. It displays the total time at the instance level spent on synchronous statistics update operations;
- The LIGHTWEIGHT_QUERY_PROFILING parameter is a new database scope parameter that enables or disables the simplified query profiling infrastructure. Simplified Query Profiling Infrastructure (LWP) —provides more efficient query performance data compared to standard profiling mechanisms. By default, it is enabled;
- The table function sys. dm_ exec_ query_ plan_ stats is a new dynamic management function (DMF), it returns the equivalent of the last known actual execution plan for all requests. It is controlled, i.e. it can be turned on and off using the parameter LAST_QUERY_PLAN_STAT at the level of the database area;
- The SHORTEST_PATH parameter is a new MATCH parameter that is used to find the shortest path between any two nodes in a graph or to perform traverses of arbitrary length;
- Graph tables now support partitioning of tables and indexes;
- UTF-8 encoding support – for import and export of encoding, as well as sorting option at the column and database level for string data;
- The Java SDK is an interface for extending the Java language that is used to communicate with SQL Server and execute Java code from SQL Server. Previously, we could execute code in the R and Python languages in the SQL server database ( how to do this, we examined in the article – How to execute Python code in Microsoft SQL Server on T-SQL ), now it has become possible to execute code in the Java language;
- CREATE EXTERNAL LANGUAGE is a new DDL statement that registers external languages such as Java;
- Improvements in Error Messages – now the default data truncation error message includes table and column names, as well as a truncated value;
- Certificate management in SQL Server Manager – now it is possible to manage certificates in SQL Server Configuration Manager;
- Five synchronous replicas in Availability Groups – the maximum number of synchronous replicas has been increased to five ( in 2017 version 3 ), which makes it possible to configure a group of five replicas to automatically switch to another resource within the group;
- Redirecting the connection from the secondary replica to the primary – this feature allows you to direct client application connections to the primary replica regardless of the target server specified in the connection string;
- Accelerated database recovery (ADR) – this improvement reduces the time to restore databases after a restart or a long rollback of transactions;
