Each new SQL Server version brings numerous extensions and improvements to the Transact-SQL language. Most of them are used to support newly added database engine features, but some of them address missing functionalities and limitations in previous versions. SQL Server 2016 and SQL Server 2017 come up with many features that require extensions in Transact-SQL: temporal tables, JSON support, improvements for memory-optimized tables, columnstore tables and indexes, new security enhancements, graph databases, and more. They will be explored in detail in the chapters dedicated to the appropriate features.
This chapter covers Transact-SQL features that can make a developer's work more productive and enhancements that can increase the availability of database objects and enlarge the scope of existing functionalities, limited in the previous SQL Server versions. In addition, it will cover how the execution plans in SQL Server 2017 are improved during compilation and after query execution.
This chapter is divided into the following four sections:
- New and enhanced Transact-SQL functions and expressions
- Enhanced DML and DDL statements
- New query hints
- Adaptive query processing in SQL Server 2017
In the first section, you will see new, out-of-the-box functions and expressions that allow developers to manipulate with strings more efficiently, to compress text by using the GZIP algorithm, and play with session-scoped variables.
The second section covers enhancements in data manipulation and data definition statements. The most important one will let you change the data type or other attributes of a table column, while the table remains available for querying and modifications. This is a very important feature for systems where continuous availability is required. You will also be aware of other improvements that let you perform some actions faster or with less written code.
The third section brings a demonstration of how to use newly added query hints to improve query execution and avoid problems caused by the Spool operator or inappropriate memory grants.
Finally, SQL Server 2017 introduces the adaptive query processing feature, which breaks the barrier between query plan optimization and actual execution, improves overall performance, and addresses issues that cause suboptimal execution plans.