Important feature in sql 2016 for developers,new in sql server 2016

Important feature in sql 2016 for developers:

What’s new in sql server 2016

In sql server 2016 has many new features. Here we are listing some......

Encrypted
Always Encrypted feature is designed to protect the data. With this feature enabled data is always be encrypted within SQL Server. Access to encrypted data is only available to the applications calling SQL Server.  This 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. Encryption and decryption of data happens transparently inside the application. This means the data stored in SQL Server will be encrypted which can secure it from DBA and administrators but that also has considerations for ad-hoc queries, reporting and exporting the data.

PolyBase 
PolyBase allows you to query distributed data sets. With this feature you will be able to use T-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.

JSON Support 
JSON stands for Java Script Object Notation. JSON (JavaScript Object Notation) is a standardizeddata exchange format that is currently not supported natively by SQL Server. With JSON Support now you can 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.

Query Store 
One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. Without comprehensive testing, this has traditionally been a hard problem to identify and then resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed. The Query Store is configured at the individual database level.

Row Level Security 
A feature that other databases have had for many years, and SQL Server has lacked natively is the ability to provide row-level security (RLS). This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID. I've seen some customized implementations of RLS at clients in the past, and they weren't pretty. It is hard to execute at scale. The implementation of RLS in SQL 2016 still has it limits (updates and inserts are not covered), but it is good start on a much-needed feature.

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.

Comments

Popular posts from this blog