SQL Server 2016, Azure SQL DB and related cloud technologies, are Microsoft’s implemented vision for a complete data platform that offers everything that has to do with data management, transformation, knowledge extraction from structured, semi-structured and unstructured data and beyond. Read on, to learn more about SQL Server 2016 Top Features.
SQL Server 2016 Top Features with Explanation
At a recent community event, I talked about SQL Server 2016 and its top features and i can assure you, SQL Server 2016 comes with a rich set of exciting new features as well as many enhancements to features shipped with earlier SQL Server releases such as In-Memory OLTP! The features I consider the most significant in the upcoming release of SQL server are:
Stretch Database
Stretch Database stores historical data in the Microsoft Azure cloud. It runs in the background and provides a seamless access to both local and remote data. Among other, it can be used for archiving processes.
Built-In JSON Support
JSON stands for JavaScript Object Notation and it is widely used for data representation in the data services world. With JSON support, you can format query results as JSON by adding the FOR JSON clause to a SELECT statement in SQL Server. Also, you can converts JSON data to rows and columns by calling the OPENJSON rowset provider function.
In-Memory OLTP Enhancements
The In-Memory OLTP Engine uses non-blocking multi-version optimistic concurrency control that is having multiple version of rows of data loaded in memory thus eliminating both locks and latches. It features Memory-optimized tables (Durable & Non-Durable) and Natively-compiled stored procedures and when they are utilized in a plethora of scenarios, they can offer significant performance boost. In-Memory OLTP was first introduced in SQL Server 2014. In SQL Server 2016 a large number of improvements is provided having as a highlight the following: support for all collations, support for DML triggers in memory-optimized tables and support for more T-SQL constructs in natively-compiled SPs.
Featured Tool: In-Memory OLTP Simulator
Query Store
With Query Store you can get insights on query plan choice and performance. You can also quickly find performance differences caused by changes in query plans.
Temporal Tables
Temporal Tables is a new type of user table. It was introduced in ISO/ANSI SQL 2011. The main role of temporal tables is to keeps a full history of data changes. This allows easy point in time analysis. In temporal tables, the period of validity for each row is managed by the system (i.e. Database Engine). Some benefits of Temporal Tables are: auditing of all data changes/data forensics, data reconstruction/recovery, trends monitoring over time, etc.
TempDB Enhancements
You can now configure TempDB via SQL Server 2016 installation wizard. For example you can set the number of data files and growth settings, as well set multiple volumes for TempDB database files. Additionally, now all TempDB file will grow at the same time so there is no need any more for the Trace flags 1117 and 1118.
PolyBase
With PolyBase you can access data stored in Hadoop or Azure Blob Storage with T-SQL Statements. As of that, you can query semi-structured data and join the results with relational data sets stored in SQL Server. PolyBase is optimized for Data Warehousing workloads and it is intended for analytical query scenarios.
Always Encrypted
This feature is designed to protect sensitive data. It allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine. To this end, through a transparent encryption for the client applications, data is not visible to the DBA. In order for the client to be able to decrypt the data you just need to install an Always Encrypted-enabled driver installed on the client computer.
Dynamic Data Masking
Dynamic Data Masking in another data protection feature in SQL Server 2016. It limits sensitive data exposure by masking it to non-privileged users. The masking rules are applied in the query results and you designate how much of the sensitive data to reveal and to who. The Dynamic Data Masking feature provides four types of masks: Default, Email, Custom String and Random. A simple example of Dynamic Data Masking is to display only the last 4 digits of a phone number to the support personnel of an IT Department.
Row Level Security
Row Level Security controls access to rows in a table based on the characteristics of the user executing a query. An example is having salesmen see only sales they did and not all the sales in a table. The access restriction logic is located in the database tier and access restrictions are applied always and cannot be skipped.
SQL Server R Services
With SQL Server’s support for R you can call the R language runtime through T-SQL and thus uncover new insights and create predictions on top of your data.
In subsequent articles I will be presenting each one of the above features with comprehensive description and many demos!
Learn What’s New in SQL Server 2019 – Enroll to our Online Course!
Check our online course titled “SQL Server 2019: What’s New – New and Enhanced Features)” (special limited-time discount included in link).
Learn about the exciting new features and enhancements in SQL Server 2019 Database. Many live demonstrations included!
Learn More
- MS Docs Article: What’s New in SQL Server 2016, November Update
Featured Online Courses:
- Introduction to Azure SQL Database for Beginners
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Read Also:
- SQL Server 2016 SP1: Breakthrough Changes
- Temporal Tables in SQL Server and Azure SQL Database
- SQL Server 2016: Built-In JSON Support
- Azure Cosmos DB: Learn by Example
- Learn Azure SQL Database: Creating your First Database
- How to Create an Azure SQL Server Virtual Machine
- How to Backup a SQL Server Database from On-Premises to Azure Storage
- What is Azure Advisor?
- … all our Azure-related articles
Check our latest software releases!
Easily generate SQL code snippets with Snippets Generator!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Secure your SQL Server instances with DBA Security Advisor.
Benchmark SQL Server memory-optimized tables with In-Memory OLTP Simulator.
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.