Total Page Preview:   000000019167

Exploring the Top 10 new features in SQL Server 2016

New Feature SQL SERVER 2016

New Features in SQL Server 2016:

  1. Query Store
  2. Polybase
  3. Dynamic Data Masking(DDM)
  4. JSON Support
  5. Stretch Database
  6. Multiple TempDB Database Files
  7. Row Level Security
  8. R Comes to SQL Server
  9. Temporal Table
  10. Always Encrypted
  11. In-Memory Enhancements

1: Query Store


The Query optimizer is one of the major problem faced by many organizations while upgrading versions of SQL Server.

This results in performance degradation. The Query Store feature allows administrators or developers to identify queries that take longer time for execution and replace them from other substitute queries.

This special feature is configured at the individual database level in SQL Server.


2: PolyBase


Polybase is an SQL Server connector to Hadoop or SQL Azure blob storage to its data warehouse appliance Analytics Platform System that has been introduced by Microsoft in 2015.

Now, it has been incorporated into its regular on-premises product of Microsoft. This feature is beneficial in dealing with a lot of large text files while processing data.


Polybase in SQL-SERVER 2016

3: Dynamic Data Masking(DDM)


Dynamic Data Masking. Especially in the Business Intelligence World or when it comes to reporting or data analytics you often need to anonymize data and don’t report on personal information. This new feature helps here.

Now, it has been incorporated into its regular on-premises product of Microsoft. This feature is beneficial in dealing with a lot of large text files while processing data.


4: JSON Support


SQL Server 2016 also supports the lingua franca of Web applications: Java Script Object Notation (JSON).It provides the ability to quickly move JSON data into tables.

By adding this support Microsoft has provided SQL Server the ability to parse JSON formatted data so it can be stored in a relation format.


SQL-SERVER Query in JSON

5: Stretch Database


“Stretch Database” is a hybrid feature introduced in SQL server in an attempt to reduce the storage and processing costs. A Stretch Database feature provides you a method to stretch the storage of your On-Premise database to Azure SQL Database

This feature works on the concept of moving, a part of your tables into an Azure SQL Database in the cloud in a secure manner. This helps the query optimizer in balancing the workload on your server and Azure.

Microsoft only charges for the SQL Database in Azure when it is used for queries.


Stretch Database In SQL-SERVER 2016

6: Multiple TempDB Database Files


It has been a best practice for a while to have more than one TempDB data file if you are running on a multi-core machine.

Up through SQL Server 2014, you always had to manually add the additional TempDB data files after you installed SQL Server.

With SQL Server 2016 you can now configure the number of TempDB files you need while you are installing SQL Server.

Having this new feature means you will no longer need to manually add additional TempDB files after installing SQL Server.


Multiple TempDB Database in SQL-Server 2016

7: Row Level Security


The row-level security (RLS) feature restricts which users can view what data in a table, based on a function. This feature is useful in multi-tenant environments where the user may want to limit data access based on customer ID.

Restricting rows will be done by filter predicates defined in inline table value function. Security policies will ensure the filter predicates get executed for every SELECT or DELETE operation.


8: R Comes to SQL Server


Microsoft’s purchase of Revolution Analytics they are now able to incorporate R to support advance analytics against big data right inside of SQL Server.

By incorporating R processing into SQL Server, data scientists will be able to take their existing R code and run it right inside the SQL Server database engine.

This will eliminate the need to export your SQL server data in order to perform R processing against it. This new feature brings R processing closer to the data.


9: Temporal Table


A temporal table is table that holds old versions of rows within a base table.

By having temporal tables SQL Server can automatically manage moving old row versions to the temporal table every time a row in the base table is updated.

The temporal table is physically a different table then the base table, but is linked to the base table.

If you have been building or plan to build your own method to managing row versioning then you might want to check out the new temporal tables support in SQL server 2016 before you go forth and build your own row versioning solution.


10: Always Encrypted


SQL Server has supported both column-level encryption, encryption at rest, and encryption in transit. They all need to be configured independently.

Always Encrypted is new functionality so the data stays encrypted in transit, at rest and while it is alive in the database.


11: In-Memory Enhancements


The concept of in-memory tables was introduced in SQL Server 2014. These were specially designed for loading data in high-speed with no locking issues or high-volume session state issues.

However, there were a lot of limitations mainly around constraints and procedures. In SQL Server 2016, this feature is improved to a great extent, supporting foreign keys, check and unique constraints and parallelism.

Moreover, tables up to 2TB are now supported (up from 256GB). Another benefit of in-memory is column store indexes, which are mostly used for data warehouse workloads.

Although, this feature was introduced in SQL 2012 and has been enhanced in each version consecutively. In 2016, it receives some enhancements around sorting and better support with AlwaysOn Availability Groups.

 

 

Thanks

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments

SANNY KUMAR
01-Jul-2016
Very nice article.
Brijesh Kumar
10-Sep-2016
This article is very useful for DBA.
Ajay Zara
10-Sep-2016
Really is this article is very good keep continue this type article.
Wittenoom
21-Jun-2017
Hi it's me, I am also visiting this web site on a regular basis, this web page is truly nice and the people are genuinely sharing fastidious thoughts.
Elliot
13-Nov-2019
Your style is unique compared to other folks I have read stuff from. Thanks for posting when you've got the opportunity, Guess I'll just bookmark this web site.
Bustamante
29-Jul-2021
I used to be able to find good advice from your content.

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: