Chapter 4 Transact-SQL Enhancements

New versions of SQL Server introduce new and enhanced SQL code functionality, some of these features are examined here, including: 

• STRING_SPLIT

• STRING_AGG

• TRIM

• TRANSLATE

• COMPRESS/DECOMPRESS

• HASHBYTES

• JSON functions 

In each case, the function is explained and its use shown with helpful SQL code.

The chapter next looks at enhanced Data Manipulation Language (DML) and Data Definition Language (DDL) statements. For the most part, these are nice to have improvements (e.g. DROP IF EXISTS), but alternatives already exist for many of these enhancements.

The chapter next looks at three new query hints. Hints are not actually hints, but are orders, they force the optimizer to follow your instructions, while this may be useful, they should be examined regularly since their use may change. Helpfully, the authors highlight this concern.

The chapter ends with a look at a new 2017 feature, adaptive query processing, which dynamically executes and replaces part of a query plan during the optimization process, producing better query performance.

Again this chapter provides a useful overview of new and enhanced SQL features.

 (click cover to purchase from Packt)

Chapter 5 JSON Support in SQL Server

JSON (JavaScript Object Notation) is a mechanism of data exchange (cf. XML), increasingly used by applications. This chapter explores what JSON is, how it is used, converting JSON data into table data, extracting data values, and modifying JSON data. It ends with a look at performance concerns associated with the current implementation of JSON. In many ways, the use of JSON is similar to how XML is used by SQL Server (but without native support).

Overall this chapter should prove useful in extending the reach of JSON-based applications.

Chapter 6 Stretch Database

As data volumes increase, query performance can decrease. Stretch Database functionality allows part of your data (the older, less queried data) to be moved to slower/cheaper storage in the cloud (Microsoft Azure), aiming to improve performance. This is done seamlessly to any application use.

The chapter opens with an overview of the Stretch Database architecture, which determines if the query runs locally and/or remotely. The Data Migration Assistant can be used to identify databases and tables that would benefit from stretching, its use is demonstrated with helpful screenshots.

Various limitations of Stretch Database are discussed. Microsoft often releases a new feature with limited capabilities, which is subsequently enhanced. Some user cases of expected usage are provided (e.g. archiving). Some example SQL code is provided to enable Stretch Database functionality. The chapter ends with a discussion of management and troubleshooting features, rounding out a useful introduction to a feature that may well save you money/resources and improve the performance of your queries.

Chapter 7 Temporal Tables

Temporal tables allow you to store versioning or temporary data, for example, changes to a customer record – this is especially useful for auditing purposes. Previously, arduous manual systems were often implemented to record these changes, now temporal tables make this much easier.

The chapter opens with a discussion about what temporal data is, together with its theoretical basis. The creation, alteration, and administration of temporal tables are discussed, and helpful SQL code provided. The chapter ends with an overview of what temporal data functionality is still missing from SQL Server.

Chapter 8 Tightening the Security

The ever-increasing breaches of application security highlights the need for improved security features. This chapter opens with an overview of some basic SQL Server security features (principles, securables, schemas, permissions). Next it looks at data encryption via the Always Encrypted feature, this prevents tools/people reading the encrypted data except via authorised application with an appropriate key. This feature is explained with helpful example code.

Row-level security, which ensures the user only sees data they have permission to access, is next to be discussed. Again, its implementation is described and illustrated with example code. The chapter ends with a look at Dynamic Data Masking, which masks data for non-privileged users.

This chapter provides useful example code that shows how the new security features are implemented, and can be adapted to your own use.

Chapter 9 Query Store

Query Store is a tool that records information about queries, resource usage, query plans, and other execution details. This tool is very useful for troubleshooting performance problems (e.g. when a query starts to run slowly).

The chapter opens with a discussion about why the Query Store is so important. This is followed with a discussion about its architecture. Code is provided to enable/disable, configure, disable, and clean the Query Store. Next, a walkthrough is provided showing how Query Store works in the real world. If a query starts to perform slowly, it is possible to use Query Store so a previous version of the query plan is used, thus restoring performance.

Various useful SSMS Query Store reports are discussed, with helpful screenshots, these identify:  

• Regressed queries

• Top resource consuming queries

• Overall resource consumption

• Queries with high variance (often identify parameter sniffing problems)  

Next, the chapter looks at the interesting Automatic Tuning feature of SQL Server 2017, with reference to the Dynamic Management View (DMV) sys.dm_db_tuning_recommendations.

The chapter ends with some useful use cases (e.g. system version upgrades, application upgrades). The automatic tuning features look particularly interesting, just be careful not to implement them without due consideration.

Chapter 10 Columnstore Indexes

Traditionally, relational databases have stored data in a row-wise fashion. Storing data in a column-wise fashion (i.e. columnstore) typically offers performance advantages (i.e. similar adjacent data enhances compression), especially on reporting systems that scan lots of data or where only a subset of columns is required.

This chapter discusses what columnstore indexes are, how they are implemented and used. All of this is found in the more recent versions of SQL Server. However, the big advances in SQL Server 2016 are the ability to update nonclustered columnstore indexes, and the use of columnstore indexes on In-Memory tables.

While the recap on columnstore indexes is useful if you’ve not used them previously, it could be argued it’s not needed in this book, which primarily discusses new and enhanced features. The chapter provides useful code illustrating the speed improvements columnstore indexes can make.

Chapter 11 Introducing SQL Server In-Memory OLTP

Getting data from tables on physical disks can be expensive, since memory is cheap it makes sense to store tables in memory (disk access is measured in milliseconds, memory access is measured in nanoseconds). With this in mind, Microsoft created In-Memory tables, which can typically improve OLTP query performance by a factor of between 10 and 30.  

This chapter provides a recap of In-Memory OLTP functionality in SQL Server 2014. It discusses the architecture of In-Memory tables, provides example code for their creation, looks at the improved concurrency model, and their access via natively compiled stored procedures. Data durability solutions and concepts are examined, together with monitoring via perfmon counters and Extended Events.

Overall, this is a useful chapter for those unfamiliar with In-Memory functionality.

Chapter 12 In-Memory OLTP Improvements in SQL Server 2017

Whilst the SQL Server 2014 In-Memory functionality seemed exciting, it contained many limitations (e.g. ALTER commands not supported), leading to its limited implementation. SQL Server 2016 removes many of these limitations, as well as extending other functionality.

The chapter opens by discussing the inability to change In-Memory OLTP objects in SQL Server 2014, this restriction has now been removed. Next, various feature improvements are discussed, including: 

• Most data types supported

• Unconstrained integrity

• More operators supported

• Improvements in the In-Memory OLTP Engine

• Large object support 

There are still limitations with In-Memory OLTP (e.g. it can’t use LIKE in natively compiled stored procedures), but the situation is significantly better than with SQL Server 2014.

The chapter ends with a look at how In-Memory OLTP features have expanded into SQL Server administration, and looks at its seamless integration into security, Query Store, Temporal Tables, High Availability, and various tools and wizards. Essentially, the division between disk or memory based tables is hidden.

This chapter provides a very useful update on the removal of many limitations in the previous version of In-Memory OLTP.