Seven Awesome SQL Server Features (That You Can Use for Free)

I am honored to have been selected to speak at Codestock on May 6, 2017, on the topic of Seven Awesome SQL Server Features (That You Can Use for Free).

Slide deck: PowerPoint (376KB) or PDF (1MB)

Demo scripts: ZIP (17KB)

Or, if you prefer, you can get both from this GitHub repo.

Session Summary

In this session I discussed how historically, the Enterprise Edition of SQL Server was the beneficiary of the best new features. Over the past few releases, the lower editions have been receiving less and less love from Microsoft. However, in 2016 they released service pack 1 of SQL Server 2016 and pushed many of these formerly Enterprise features into all of the lower editions. We took a look at several features that were either new to version 2016 or newly available in these lower editions – including the free Express Edition – starting with SP1.

  • Row-Level Security allows us to define who can see or update a row based upon a user-defined security predicate.
  • Always Encrypted keeps sensitive data entirely away from the SQL Server instance. All data is encrypted client-side using the database drive, so all SQL Server ever transmits or stores is the ciphertext.
  • Snapshots are a read-only point-in-time view of a database. They have many uses, but we focused on using this feature to improve QA/Test cycles.
  • Temporal Tables allow us to capture the history of changes to data over time. They also provide an easy syntax to look backward and see the state of the data as of some specified time in the past.
  • Columnstore is a way of indexing table data by column rather than the traditional row-based methods. This often results in highly compressed structures that can be accessed in a quite efficient manner.
  • Partitioning is an older technology now available in all editions. While this feature has many uses, we discussed it in the context of making archiving of temporal table data more efficient.
  • Hekaton, or In-Memory OLTP is a relatively new type of table in SQL Server that uses memory as the primary storage location for the data. The way the rows are accessed represents a big paradigm shift, notably by using lock-free structures. It also allows for stored procedures to be compiled to native machine code.