Things new in SQL Server 2016

In SQL Server 2016,

Configure multiple TempDB database files during Installation and set up.

https://hodentekmsss.blogspot.com/search?q=TempDB

The Query Store (new) stored texts, execution plans and performance metrics with the database. You have access to its dashboard related to query performance.

https://hodentekmsss.blogspot.com/search?q=query+store

[image]

Availability of Temporal Tables (history) which records all data changes.

https://hodentekmsss.blogspot.com/2016/07/temporal-tables-in-sql-server-2016-to.html

Built-in JSON Support(new). You can import/export, save and parse in JSON.

https://hodentekmsss.blogspot.com/2016/11/accessing-nested-json-formatted-text.html

Polybase(new) query engine integrated SQL Server with external data in Hadoop or Azure Blob storage. Import/export and executing queries all possible.

https://hodentekmsss.blogspot.com/search?q=polybase

Stretch Database(new) lets you dynamically, securely archive data from local SQL Server Database to an Azure cloud SQL database. querying is automatic both local and remote data by linked databases.

https://hodentekmsss.blogspot.com/2016/05/stretch-database-is-nice-feature-of-sql.html

In-memory OLTP:

Now supports FOREIGN KEY, UNIQUE and CHECK constraints, and native compiled stored procedures OR, NOT, SELECT DISTINCT, OUTER JOIN, and subqueries in SELECT.

Supports tables up to 2TB (up from 256GB).

Has column store index enhancements for sorting and Always On Availability Group support.

New security features:

Always Encrypted: When enabled, only the application that has the encryption key can access the encrypted sensitive data in the SQL Server 2016 database. The key is never passed to SQL Server.

Dynamic Data Masking: If specified in the table definition, masked data is hidden from most users, and only users with UNMASK permission can see the complete data.
https://hodentekmsss.blogspot.com/2018/05/data-masked-column-in-table.html

https://hodentekmsss.blogspot.com/2017/07/new-security-feature-in-sql-server-2016.html

Row Level Security: Data access can be restricted at the database engine level, so users see only what is relevant to them.