Introducing SQL Server 2019

Article archived

26th September 2019

An overview of the new Microsoft SQL Server 2019 features for the database engine that brings innovative performance, security and compliance features and enhancements.

The release of SQL Server 2019 is around the corner.  What can you expect from this shiny new version?

Well, Microsoft has taken the new features that came in SQL 2016 and 2017 and added to and enhanced them to produce a database offering that can do so much more for you than anything we’ve seen before. Including the ability to do Realtime analytics on both your traditional SQL Server relational database engine and on semi-structured and unstructured data using Big Data Clusters

To quote a famous Microsoft SQL Server employee from SQLBits… There are several enhancements and improvements in the “Good old Grandad SQL Server.”

Expect SQL Server 2019 to run faster than previous versions

The Intelligent Query Processing, which was introduced with SQL 2016 has been improved.

Previous versions of SQL Server have always assumed that a table variable will only have one row in it which can cause bad execution plans to be created. Introducing Table Variable Deferred Compilation. Now the saving of the execution plan will be delayed until after the first run of the query to see how many rows go into the table variable. The next time the query is run the query processor will have a better idea of how to create the execution plan.

Approx Count Distinct

If you’ve ever had to do select distinct you know how long it can take to return data. SQL Server 2019 now has a new command called APPROX_COUNT_DISTINCT. This is much more lightweight and can return a number much faster based on the table statistics. This is great if you have a very large table and just need a rough value, but the name gives it away, it is an approximation, so it is no good if you need to know the exact value.

Scalar UDF Inlining

It is widely known that using user-defined functions in a query can destroy the performance and as such is highly discouraged. Well in SQL Server 2019 the query processor will now use the UDF in a better way to quote the Microsoft documentation “transforms scalar UDFs into relational expressions and embeds them in the calling SQL query.” which allows for vastly improved performance for UDFs.

In-memory OLTP for system tables

You can now use memory optimised (In-Memory OLTP) tables in TEMPDB. This means we can avoid or at least reduce contention in TEMPDB, one can be one of the big bottlenecks on a SQL Server and can make a huge difference to performance.

Memory grant feedback.

If the statistics on your table are wrong or stale, then the query processor may assign too much or too little memory to a query. The processor will now take notice of this and reallocate the memory next time that query is run.

Expect SQL Server 2019 to be more secure

Always encrypted has suffered from not being able to do range searches. This has now been fixed by something called secure enclaves. This is a protected area of memory on the SQL server which the server can use to decrypt the encrypted data so that I can do range searches then return the encrypted values to the client. It does require Windows Server 2019 and specific server hardware

Static Data Masking: There were plenty of ways of getting around dynamic data masking to view the or work out the underlying data. Static data masking is an enhancement to SSMS that allows you to create a copy of the database with masked data so there is no way of reverse engineering the original data.

Sensitivity classification: The need to classify personal identifiable data has become much more important since the introduction of GDPR. You can add a label and information type to individual columns so you can now tag columns with a description such as GDPR or just important internal data

You can then create SQL Audits so you can now see who accessed confidential data and when. Really useful for your GDPR audits.

Expect SQL Server 2019 to be easier to administer and manage

Many server level configurations, such as Optimize for ad hoc Workloads can now be set at the database level providing a more granular configuration for your consolidated SQL Servers

We now have a DMV and DMF, called PageResCracker, that allows you to see data page information without the need to use DBCC commands. These don’t require sysadmin permissions and make investigating page locks much easier.

You can now get estimates of potential compression of a new ColumnStore index before you create it.

Resumable / online operations have been expanded. Previously only re-indexes could be done online but we can create index with online option. We can also pause and resume the index creation and rebuilds. Allowing re-indexing operations to be spread over several maintenance windows rather than it carrying on into the peak time workload.

We can now do online creation and rebuilding of clustered columnstore indexes. This is an enterprise only feature.

Accelerated Database Recovery. When a database or a large update query has crashed it can sometimes take a long time for SQL to recover the database back to a working state. Now the database has new features so recovery can now be completed almost instantly regardless of how big the database transaction is.

Expect SQL server 2019 to have improved programmability

• UTF-8 support for varchar columns
• Graph database, introduced in SQL 2017 has new features: edge node constraints and merge
• Inbuilt Java support
• Polybase has been extended to include support for many more data sources including MongoDB, Teradata, Spark Hadoop Teradata and HDFS file stores so you can now query relational and non-relation data directly from within the SQL engine.

And the most requested improvement to SQL server-ever.

The “String or binary data will be truncated” error message will now tell you which table and column that is causing the problem and the truncated value that can’t be inserted. This does require a trace flag (460).

For more information, please contact us