New features in SQL 2016

Below are the new features introduced in SQL 2016:

1. Always Encrypted
2. Polybase
3. Stretch Database
4. In-Memory OLTP
5. Columnstore indexes
6. Dynamic Data Masking
7. Multiple TempDB files
8. Live Query Statistics
9. JSON Support

AlwaysEncrypted:

Always Encrypted is designed to allow encrypted data to always be encrypted but still allow SQL Server to work with the data.

Polybase:

It allows you to access the data available in Hadoop through T-SQL commands.

Stretch Database:

It allows you to access the latest data from local server and historical information from Azure DB.

We can use this feature whenver we have less space in our local server to store the data.

In-Memory OLTP:

There are many enhancements to In-Memory OLTP in SQL 2016 such as increase in size of the table, alteration to the existing tables etc..

Columnstore Indexes:

It was introduced in SQL Server 2012 and enhanced in 2014 and 2016.

Columnstore index stores the data in terms of columns instead of rows.

Find below the enhancements that were made in SQL 2016:

1. A Table will still have one NonClustered ColumnStore Index, but this will be updatable so we can also update table

2. Now you can create a Filtered NonClustered ColumnStore Index by specifying a WHERE clause to it.

3. A Clustered ColumnStore Index can have one or more NonClustered Indexes.

4. Clustered ColumnStore Index can now be Unique indirectly as you can create a Primary Key Constraint on a Heap table (and also Foreign Key constraints).

5. MS allowed us to create column store index on memory optimized tables but you need to adhere to following rules:

1. It should be created while we are creating memory optimized table itself
2. It should include all the columns available in the table
3. No filtered index allowed that means we need to include all the rows 

Dynamic Data Masking:

It is about masking of the data available in columns.

There are three ways to mask the data:

a. default()

b. email()

c. partial()

all the above functions will provide you the customizations to the masked data.

For more information refer Dynamic Masking in SQL 2016

Multiple TempDB files:

We can have more than tempdb data files in SQL Server 2016.

By default number of data files will be equal to the number of cores available in CPU OR 8 which ever is minimum, it will be set to that.

The primary file for tempdb will be temp.mdf. Others will be names as tempdb_mssql_#.ndf where # represents the unique identification number for each data file.

We need to mention the tempdb file count when we are rebuilding system databases as it won't persist. We cab provide the count of tempdb files with SQLTEMPDBFILECOUNT parameter. If it is not provided it will take the default value.

Live Query Statistics:

SQL Server 2016 provides you with the live statistics of currently active queries.

JSON Support:

SQL 2016 introduced new clause FOR JSON to convert the resulted data into JSON format.

For more details refer FOR JSON in SQL 2016

To know about the improvements in SSIS in SQL 2016 go through improvements in SSIS in SQL 2016