New Features of SQL Server 2019
It’s that time again to talk about the New Features of SQL Server 2019. Years ago, there was no need to keep so up-to-date with the New Features of SQL Server due to the release dates of each version (I’m talking about you SQL Server 2000 and SQL Server 2005) between each version. Now, with a much increased release cycle it is critical for any SQL Server DBA to stay in touch with these New Features. Of course, you don’t have to know about all the details of the new features, you just have to know what is there. As the Merovingian would say in the Matrix Reloaded, who has time?
Matrix and SQL Sequels
Well, that time has now come (as well as a new Matrix sequel, yay) and SQL Server was released towards the latter part of last year on the 4th November 2019.
You may think that SQL Server 2019 is an unnecessary release. I believe Microsoft are showing their dominance of the RDBMS market and not resting on their laurels but forging ahead with new and innovative features.
The New Features list is long, so I’m going to add all the new features here from the Microsoft post – What’s new in SQL Server 2019 (15.x)
New Features Path
My plan, and there must always be a path, Neo, is to go through the most relevant items and have a play and document my findings here, so if there’s a hyperlink then I’ve looked at it, click it, have a read, let me know.
It will also form a great reference list of any new Sequallity SQL Server 2019 training modules (SSRS, DBA, TSQL, Performance Tuning) I have to build going forward to include the up-to-date features of SQL Server 2019.
There’s so much to do…with these New Features
There’s no need to feel inundated, I don’t. I am excited by the challenge these new features bring and I am looking forward to the days when a client says, we need this and I go ah-ha, there’s a great new feature in SQL Server 2019 that does that. To be fair, been there already only a month after SQL Server 2019’s release date and the winner was…Optimize For Sequential Key.
However, I tuned the database so well, we didn’t need to implement this new feature, which is a shame but it meant the client could stay on their 2016 version if they wanted, they didn’t mind going to SQL Server 2019. If performance degrades again, we’ve got this in my bag if needed.
It’s always good to have a Plan B.
Big Data Clusters
Scalable big data solution
Data virtualization with PolyBase
Intelligent Query Processing
Row mode memory grant feedback
Batch mode on rowstore
Scalar UDF Inlining
Table variable deferred compilation
Approximate query processing with APPROX_COUNT_DISTINCT
In-Memory Database
Hybrid buffer pool
Memory-optimized TempDB metadata
In-Memory OLTP support for Database Snapshots
Intelligent performance
OPTIMIZE_FOR_SEQUENTIAL_KEY
Forcing fast forward and static cursors
Resource governance
Reduced recompilations for workloads
Indirect checkpoint scalability
Concurrent PFS updates
Scheduler worker migration
Monitoring
WAIT_ON_SYNC_STATISTICS_REFRESH
Custom capture policy for Query Store
LIGHTWEIGHT_QUERY_PROFILING
sys.dm_exec_requests column command
sys.dm_exec_query_plan_stats
LAST_QUERY_PLAN_STATS
query_post_execution_plan_profile
sys.dm_db_page_info(database_id, file_id, page_id, mode)
Graph
Edge constraint cascade delete actions
New graph function – SHORTEST_PATH
Partition tables and indexes
Use derived table or view aliases in graph match query
Unicode support
Support for UTF-8 character encoding
Language extensions
New Java language SDK
Java language SDK is open source
Support for Java data types
New default Java Runtime
SQL Server Language Extensions
Register external languages
Spatial
New spatial reference identifiers (SRIDs)
Error messages
Verbose truncation warnings
Security
Always Encrypted with secure enclaves
Certificate management in SQL Server Configuration Manager
Data Discovery & Classification
Availability Groups
Up to five synchronous replicas
Secondary-to-primary replica connection redirection
HADR Benefits
Recovery
Accelerated database recovery
Resumable operations
Online clustered columnstore index build and rebuild
Resumable online rowstore index build
Suspend and resume initial scan for Transparent Data Encryption (TDE)
Linux
Replication support
Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
OpenLDAP support for third-party AD providers
Machine Learning Services on Linux
TempDB improvements
PolyBase on Linux
Change Data Capture (CDC) support
Containers
Microsoft Container Registry
Non-root containers
Red Hat certified container images
PolyBase and Machine Learning support
Setup Options
New memory setup options
New parallelism setup options
SQL Server Machine Learning Services
Partition-based modeling
Windows Server Failover Cluster
SQL Server Analysis Services
Calculation groups in tabular models
Query interleaving
Many-to-many relationships in tabular models
Property settings for resource governance
Governance setting for Power BI cache refreshes
Online attach
SQL Server Integration Services
Flexible file task
Flexible file source and destination
SQL Server Master Data Services
Support for Azure SQL Database managed instance databases
New HTML controls
SQL Server Reporting Services
Azure SQL Managed Instances
Power BI Premium datasets
enhanced accessibility
Azure Active Directory Application Proxy
Transparent Database Encryption
Microsoft Report Builder Updated