Microsoft SQL Server 2016 Database Engine Features and Use Cases

The estimated reading time for this post is 3 minutes

This latest release of SQL Server introduces new features that makes it powerful and robust RDMS for enterprise companies. It is needed to mention that skillful DBAs can implement some of the new features in prior versions of SQL Server.

Lets check what is new in SQL Server 2016 DB Engine and what is the usage.

1- Columnstore Index: This feature introduced during SQL Server 2012 with lot of limitations such as it could only be non-clustered and not updatable. In SQL Server 2016 most of the limits removed and also it can be created beside Clustered Index on a table and DBAs can create Updatable Non-Clustered Columnstore Index as well. Also it can be filtered index to provide better cardinality. One of the best improvement is that In-Memory Optimized Tables can be benefited from Columnstore Index;

Use Case: It can be used on table used by OLTP and Analytical systems to boost up the performance, but it might decrease the performance if it is used in wrong place or designed wrongly.

2- In-Memory OLTP: One of the major worries of DBAs in SQL Server 2014 was the limit of the In-Memory Optimized Table size that was 256GB, which it is increase up to 2TB in SQL Server 2016 and the second major improvement is that DBAs can ALTER In-Memory Optimized Tables without losing data.

Use Case: It is great for highly transactional OLTP databases which the data loss is not that important or critical for the business.

3- FOR JSON: SQL Server 2005 introduced FOR XML clause which SQL Server can generate XML from the returned data set. Now SQL Server 2016 introduces FOR JSON to generate JSON from the returned data set. This feature can allow Java Developers to transfer JSON from/to SQL Server and use JSON Formatter in their application. This feature in SQL Server 2016 CTP 2.0 is unable to read JSON and change it to data set.

Use Case: It is great option for complex bulk load.

4- Always Encrypted: This feature is new in SQL Server 2016 but not new application development. In SQL Server 2016, application developers can encrypt their data from application via Column Encryption Key object in SQL Server. By enabling this feature in your application and SQL Server, you might have performance issues regarding retrieving range of encrypted data or unencrypted data.

Use Case: To encrypt sensitive data which DBAs always view data as binary.

Warning: If the DBA is also programer, he/she can manage to view and update all encrypted data.

5- Stretch DB: This feature is great to archive the cold data into the Azure SQL Database, This feature is new and it has few limits and issues such as it does not migrate the cold data straight away after configuration, it takes time to upload the data and retrieving data from Stretch Enabled Tables are quiet slow. The major limit is that only can Stretch Database or Table into Azure SQL Database not other on-premise SQL Server.

Use Case: To archive the cold data and at same time can retrieve archived data.

6- Row-Level Security: This is one of the features that I implement it before :). As the feature name describes, it filter data rows base on the row owner, which it can be possible by creating Views in prior versions.

Use Case: I believe you know the use case.

7- Dynamic Data Masking: This is another feature that reflects application development efforts. Database developers can mask the return data easily by using this feature. It has some limits which developers cannot create their customized masking function.

Use Case: It can be used to mask sensitive data such as CreditCard Number, Email, Telephone, Name, and etc.

8- High Availability: There are few enhancements in this feature such as Group Managed Service Accounts are supported, Load Balancing for Read-Only Replicas,Number of replicas that support automated failover increased to three, DTC support and offline database will be failover as well.

Use Case: For highly transactional environment that RTO and RPO must be almost 0 seconds.

Author: Hamid Jabarpour Fard

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:

Leave a Comment

Be the First to Comment!

Notify of