SQL Server 2017 (also known as SQL Server vNext) and the new Azure SQL Database are finally here and they are generally available! In subsequent articles we will analytically examine every new feature using many examples.
In this post, we will talk about SQL Server 2017 Database Engine. We will quickly go through the main exciting new features and improvements in order to get the big picture of how SQL Server evolved when it comes to the Database Engine.
However, before we start talking about the Database Engine, let’s first realize the huge change: SQL Server 2017 can run on Windows, Linux and Docker Containers!
Now that the above is clear, let’s see the list of the main new features and improvements in SQL Server 2017 Database Engine:
- Adaptive query processing: With adaptive query processing, SQL Server Database Engine adopts optimization strategies during the execution of your workload by taking into consideration the runtime conditions. Adaptive query processing features currently available are: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions.
- Automatic tuning: It is a mechanism which notifies you about potential performance issues, suggests solutions and lets you apply corrective actions or lets the Database Engine to automatically fix the detected performance problems.
- Cross-Database Transactions now supported in Always On Availability Groups: Ever wanted to use Always On Availability Groups but hesitated as you didn’t know how to handle systems with more than one databases which made use of cross-database transactions? This is not a problem anymore as SQL Server 2017 supports cross-database transactions for availability groups within the same SQL Server instance.
- Additional Availability Groups functionality: Clusterless support, minimum replica commit Availability Groups setting, and Windows-Linux cross-OS migrations and testing.
- Resumable online index rebuild: You can resume a paused online index rebuild operation or pause it in order to later resume it.
- In-Memory OLTP Enhancements:
- Support for computed columns in memory-optimized tables
- Full support for JSON functions in natively compiled stored procedures
- Support for the CROSS APPLY operator in natively compiled modules
- Elimination of the 8-index limitation for memory-optimized tables
- sp_rename is now supported for memory-optimized tables and natively-compiled T-SQL modules
- CASE and TOP (N) WITH TIES are supported in natively-compiled T-SQL modules
- Support for backing up, storing and restoring memory-optimized filegroup files in Azure storage.
- CLR assemblies enhancements: Ever wanted to extend your SQL Server’s functionality using CLR assemblies but along with that wanted to have more control over the security aspect of this? In SQL Server 2017 you can set trusted assemblies by adding them to whitelists. Related new sp_configure option: ‘clr strict security’.
- IDENTITY_CACHE option: By turning this setting to ON, you can avoid gaps in identity columns in cases of unexpected SQL Server restarts or failovers.
- Other cool new features and enhancements:
- New graph database capabilities
- New sp_configure option called clr strict security
- More control over the size of each TempDB file during Setup (max initial size per file supported: 256 GB)
- Database Tuning Advisor (DTA) enhancements and performance improvements
- New string functions: CONCAT_WS, TRANSLATE, and TRIM
- SELECT INTO T-SQL syntax enhancements
- New dynamic management views:
- …and much more!
For the full list of what’s new in SQL Server 2017 Database Engine please read this Microsoft Docs article.
For the full list of what is new in SQL Server 2017 (all components) please read this Microsoft Docs article.
This post was just a summary on what’s new in SQL Server 2017 Database Engine along with adding short descriptions for the nee features and enhancements. As mentioned in the beginning of the post, in subsequent articles we will analytically examine every new feature and enhancement, not only for the Database Engine but also for all the other components of SQL Server 2017.
Stay tuned #SQLServer friends!
Rate this article:
Reference: SQLNetHub (https://www.sqlnethub.com)
Recommended eBooks on SQL Server: