Find the best tutors and institutes for SQL Server

Find Best SQL Server Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

New Features Worth Exploring in SQL Server 2016

Amitava Majumder
16/02/2017 0 0

New Features Worth Exploring in SQL Server 2016

There is a lot of buzz around SQL Server 2016. Microsoft announced the release of SQL Server 2016 at the Microsoft Ignite Conference during the first week of May 2015.

In this article I will be exploring, at a very high level, 10 of those new features.

Always Encrypted- With the Always Encrypted feature enabled your SQL Server data will always be encrypted within SQL Server. Access to encrypted data will only be available to the applications calling SQL Server. Always Encrypted enables client application owners to control who gets access to see their applications confidential data. It does this by allowing the client application to be the one that has the encryption key. That encryption key is never passed to SQL Server. By doing this you can keep those nosey Database or Windows Administrators from poking around sensitive client application data In-Flight or At-Rest. This feature will now allow you to sleep at night knowing your confidential data stored in a cloud managed database is always encrypted and out of the eyes of your cloud provider.

Dynamic Data Masking- If you are interested in securing your confidential data so some people can see it, while other people get an obscured version of confidential data then you might be interested in dynamic data masking. With dynamic data masking you can obscure confidential columns of data in a table to SQL Server for users that are not authorized to see the all the data. With dynamic data masking you can identify how the data will be obscured. For instance say you accept credit card numbers and store them in a table, but you want to make sure your help desk staff is only able to see the last four digits of the credit card number. By setting up dynamic data masking you can define a masking rules so unauthorized logins can only read the last four digits of a credit card number, whereas authorized logins can see all of the credit card information.

JSON Support- JSON stands for Java Script Object Notation. With SQL Server 2016 you can now interchange JSON data between applications and the SQL Server database engine. 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. Additionally, with JSON support you can take relational data, and turn it into JSON formatted data. Microsoft has also added some new functions to provided support for querying JSON data stored in SQL Server. Having these additional JSON features built into SQL Server should make it easier for applications to exchange JSON data with SQL Server.

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. In the past, 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.

PolyBase- PolyBase allows you to query distributed data sets. With the introduction of PolyBase you will be able to use Transact SQL statements to query Hadoop, or SQL Azure blob storage. By using PolyBase you can now write adhoc queries to join relational data from SQL Server with semi-structured data stored in Hadoop, or SQL Azure blob storage. This allows you to get data from Hadoop without knowing the internals of Hadoop. Additionally you can leverage SQL Server’s on the fly column store indexing to optimize your queries against semi-structured data. As organizations spread data across many distributed locations, PolyBase will be a solution for them to leverage SQL Server technology to access their distributed semi-structured data.

Query Store- If you are into examining execution plans than you will like the new Query Store feature. Currently in versions of SQL Server prior to 2016 you can see existing execution plans by using dynamic management views (DMVs). But, the DMVs only allow you to see the plans that are actively in the plan cache. You can’t see any history for plans once they are rolled out of the plan cache. With the Query Store feature, SQL Server now saves historical execution plans. Not only that but it also saves the query statistics that go along with those historical plans. This is a great addition and will allow you to now track execution plans performance for your queries over time. 

Row Level Security- With Row Level Security the SQL database engine will be able to restrict access to row data, based on a SQL Server login. 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. Implementing row level security at the database layer means application developers will no longer need to maintain code to restrict data from some logins, while allowing other logins to access all the data. With this new feature, when someone queries a tables that contains row level security they will not even know whether or not any rows of data were filtered out.

R Comes to SQL Server- With 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.

Stretch Database- The Stretch Database feature provides you a method to stretch the storage of your On-Premise database to Azure SQL Database. But having the stretch database feature allows you to have your most frequently accessed data stored On-Premise, while your less accessed data is off-site in an Azure SQL databases. When you enable a database to “stretch” the older data starts moving over to the Azure SQL database behind the scenes. When you need to run a query that might access active and historical information in a “stretched” database the database engine seamlessly queries both the On-Premise database as well as Azure SQL database and returns the results to you as if they had come from a single source. This feature will make it easy for DBA’s to archive information to a cheaper storage media without having to change any actual application code. By doing this you should be able to maximize performance on those active On-Premise queries.

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’ve 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.

0 Dislike
Follow 0

Please Enter a comment

Submit

Other Lessons for You

Troubleshooting SQL Server Agent failed Jobs - Common daily routine work for DBA
If you are lazy and not intended to do repetitive manual work at a routine time, then SQL Server Agent job help you achieve this. SQL Server agent jobs help to automate the execution of the recurring task...

Meetopus | 06 Aug

1 0
0

Why Windows Administration knowledge is required for SQL DBA.
As the RDBMS system runs on the windows server operating system and for most of its function is depended on windows OS. Hence SQL DBA should know the basic windows to review various services, events and...

Meetopus | 06 Aug

0 0
0

What is database backup? How backup works in SQL Server?
Database Backup in normal terms can be said as a copy of data which can be used in a situation when the things go wrong with your system/server. If your role is IT/Database Administrator, then it is your...

Meetopus | 06 Aug

0 0
0

Database Origins
The need for a database originates from the fact that computers are dumb devices having batman like abilities which can be smartly be utilised by feeding it the right content. That is, for example, consider...
M

Manish Joshi | 20 Jul

0 0
0

What Is Power Query?
Power Query is an Excel add-in that can be used for data discovery, reshaping the data and combining data coming from different sources. Power Query is one of the Excel add-ins provided as part of Microsoft...

Find Best SQL Server Training?

Find Now »

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more