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, 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 is discussed, and helpful SQL code provided. It ends by with an overview of what temporal data functionality is still missing from SQL Server (e.g. a retention period).

Chapter 8 Tightening the Security

The ever-increasing breaches of application security highlights the needs 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 new 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 code that shows how the new security features are implemented.

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

• Tracked queries

The chapter ends with some useful use cases (e.g. system version upgrades, application upgrades).

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.

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 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 2012 and 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.

Chapter 12 In-Memory OLTP Improvements in SQL Server 2016

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. In many ways 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.

Chapter 13 Supporting R in SQL Server

R is a popular language used in data science, with extensive mathematical functionality. Additionally, it has data visualization capabilities for easier reporting. SQL Server has integrated R support into its database engine.

The chapter opens with an overview of where R is used (e.g. Machine Learning), followed by a useful tutorial on the basics of the R language. Next, various data structures are introduced, including array and matrices, factors, data frames, and lists. Examples are provided on data manipulated.

The chapter moves on to show how data can be expressed visually, using simple graphs and histograms. This is followed by the use of various statistical functions (e.g. mean, min, quartile, standard deviation). It ends with a look at the SQL Server R service, which provides statistical functionality to extend existing business intelligence services (e.g. SSAS), having provided a very useful introduction to using R in SQL Server.

Chapter 14 Data Exploration and Predictive Modeling with R in SQL Server

This chapter builds on the basics provided in the previously chapter, and shows how to use R in advanced data exploration, statistical analysis, and predictive modelling. These features can greatly enhance SQL Server’s analysis capabilities.

The chapter explores some intermediary-level statistics (chi-squared test, null hypothesis, linear regression, ANOVA), providing example R code to illustrate the theory. This is followed with data mining/machine learning techniques (supervised and unsupervised), examining Principal Components Analysis (PCA), Exploratory Factor Analysis (EFA), and K-mean clustering. Similarly, predictive analysis and decision trees are discussed from a theoretical viewpoint. The chapter ends with a look at some useful advanced graphing.

I suspect if your maths knowledge is limited, you’ll have to look elsewhere to get a better understanding of the underlying maths discussed.

Conclusion

This book aims to introduce you to the salient new and enhanced features in SQL Server 2016, and succeeds. It is generally easy to read, well written, with useful explanations, tips, example code, and diagrams.

Whilst the book doesn’t cover all the new features (e.g. PolyBase), it does cover the major ones. I suspect the more you know about SQL Server already, the more useful this book will be. Since the book focuses on new and enhanced features, large subject areas are omitted (e.g. database design).

Sometimes, before discussing an extended feature (e.g. In-Memory OLTP), a large amount of background information is given with reference to previous editions of SQL Server. Whilst this may be useful if you don’t know the feature, it could be argued it is unnecessary - if you already know SQL Server 2014.  

It might have been useful to include a section discussing SQL Server changes in terms of some of the industry’s wider trends (e.g. Big Data, Social Media, the Cloud).

Overall, if you want to know more about the new and enhanced features in SQL Server 2016, I can recommend this well-written book.