Chapter 4. Transact-SQL Enhancements

Each new SQL Server version brings numerous extensions and improvements to 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 comes 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, and so on. They will be explored in detail in chapters dedicated to appropriate features.

This chapter covers small Transact-SQL features that can make developers' 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.

This chapter is pided into the following three sections:

• New and enhanced Transact-SQL functions and expressions
• Enhanced DML and DDL statements
• New query hints

In the first section, you will see new out-of-the-box functions and expressions that allow developers string manipulations, compressions with the GZIP algorithm, and playing with session-scoped variables. In addition, the removal of restrictions or limitations of some functions makes them more interesting for usage in SQL Server 2016.

The further sections cover 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.

Finally, there will be a demonstration on how to use newly added query hints to improve query execution and avoid problems caused by the Spool operator or inappropriate memory grants.