SQL Server 2019: Features for the Rest of Us

I was talking recently with a friend about features of SQL Server 2019 (still in CTP at the time of this writing). I thought that while Microsoft provides features that will benefit any size shop, the exciting new feature they are promoting is Big Data Clusters. This feature allows you to query data from many other sources such as HDFS right from SQL Server with T-SQL without moving that data around. We’ve had PolyBase since 2016 which also lets you do that, but the “clusters” part of the name means running SQL Server, Spark, or HDFS in containers in Kubernetes.

I can’t see small or medium companies having much interest in this feature. My friend pointed out a handful of local marketing organisations that make their money just by analysing data. They might benefit from Big Data Clusters. I agreed that, if your company is analysing large amounts of data that is not in SQL Server, this is a brilliant solution.

Since I’ve worked with companies in a variety of industries and sizes, I’m more interested in the smaller features that make SQL Server better for everyone or solve some problems that have been around for a while. One thing that has improved over the many versions is the installation options. You can now specify max memory and max degree of parallelism during installation. Microsoft even has some recommended settings for you. Of course, these are just suggestions that may need to be tweaked based on workload and other instances running on the same server.

One feature that will save hours of troubleshooting is the new truncation error message. The old message “String or binary data would be truncated” didn’t give you any information about how to find the offending value or even which table is involved. The new message “String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’” is going to save so much time that it might seriously be considered worth the price of an upgrade.

Microsoft continues to add to the Intelligent Query Processing features started with 2017. My favourite new feature in this group is Batch Mode on Rowstore which I wrote about as a way to improve the performance of some T-SQL window functions. Some patterns that are often problematic for performance, table variables and user-defined scalar UDFs for example, benefit from Intelligent Query Processing as well. This feature group covers quite a few innovative improvements, and I can’t wait to see what the SQL Server team comes up with next!

SQL Server provides many ways of working with data, from the traditional tables and indexes to graph databases, in-memory databases, partitioned tables, JSON, and more. Each version of SQL Server brings enhancements to these features, and 2019 is no exception. An exciting new option used with CREATE INDEX is OPTIMIZE_FOR_SEQUENTIAL_KEY. This option ends last page and other hot spot contention in the B-Tree index structure. This feature can finally end the GUIDS vs identity columns as index key arguments!

There is also one feature I would never have dreamed about, much like SQL Server running on Linux starting in 2017. In 2019, you can run JAVA code in SQL Server using sp_execute_external_script! I’m curious to learn how well this will be adopted.

Be sure to keep up with all the changes and enhancements on the way with SQL Server 2019, and I’ve just mentioned a few here. There is a lot to love no matter what your size or industry!

 

The post SQL Server 2019: Features for the Rest of Us appeared first on Simple Talk.

from Simple Talk https://ift.tt/2NJsb8D
via