Monthly Archives: July 2017

Whats New in SQL Server 2016 Database Engine

Posted in General by
  • You can now configure multiple tempDB database files during SQL Server installation and setup.
  • New Query Store stores query texts, execution plans, and performance metrics within the database, allowing easy monitoring and troubleshooting of performance issues. A dashboard shows which queries consumed the most time, memory or CPU resources.
  • Temporal tables are history tables which record all data changes, complete with the date and time they occurred.
  • New built-in JSON support in SQL Server supports JSON imports, exports, parsing and storing.
  • New PolyBase query engine integrates SQL Server with external data in Hadoop or Azure Blob storage. You can import and export data as well as executing queries.
  • The new Stretch Database feature lets you dynamically, securely archive data from a local SQL Server database to an Azure SQL database in the cloud. SQL Server automatically queries both local and remote data in the linked databases.
  • 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.
    • Row Level Security: Data access can be restricted at the database engine level, so users see only what is relevant to them.