MICROSOFT SQL SERVER 2016 STANDARD

SQL Server 2016 database engine

• You can now configure multiple tempDB database files during SQL Server installation and configuration.
• The new Query Store feature lets you store query texts, execution plans and performance metrics within the database, so you can easily monitor and fix performance problems. A dashboard displays queries that use the most amount of time, memory, or CPU resources.
• Time tables are chronological tables in which all data changes are recorded, including the date and time when they occurred.
• The new JSON support built into SQL Server supports import, export, analysis and JSON archiving.
• The new PolyBase query engine allows you to integrate SQL Server with external data in Hadoop or in the Azure BLOB archive. You can import and export data, as well as query.

The new Stretch Database feature allows you to store data dynamically and securely from a local SQL Server database to an Azure SQL database in the cloud. SQL Server allows you to automatically run queries on local and remote data in linked databases.

OLTP in memory:

• The FOREIGN KEY, UNIQUE and CHECK constraints are now supported and the native stored procedures OR, NOT, SELECT DISTINCT, OUTER JOIN, as well as subqueries in SELECT.
• Tables up to 2 TB (from 256 GB) are supported.
• Improvements are available for the columnstore index for sorting and support for AlwaysOn availability groups.

New security features:

• Always Encrypted: When this feature is enabled, only the application with the encryption key can access sensitive data encrypted in the SQL Server 2016 database. The key is never passed to SQL Server.
• Dynamic Data Masking: If specified in the table definition, the masked data is hidden for most users and only users with UNMASK authorization can view the complete data.
• Row-level security: Data access can be limited at the database engine level so that users see only relevant items.
• See Database Engine.
• SQL Server 2016 Analysis Services (SSAS)
• SQL Server 2016 Analysis Services offers better performance, creation capabilities, database management, filtering, processing, and more for tabular model databases based on the 1200 compatibility level.
• SQL Server R Services integrates in SQL Server the programming language R, used for statistical analysis.
• The new database consistency verification (DBCC) functionality is performed internally to detect possible data corruption issues.
• The direct query feature, which allows you to query dynamic external data instead of importing it first, now supports multiple data sources, including SQL Azure, Oracle and Teradata.
• Several new DAX (Data Access Expressions) features are available.
• The new Microsoft.AnalysisServices.Tabular namespace manages instances and patterns of tabular mode.
• Analysis Services Management (AMO) was refactored to include a second assembly, Microsoft.AnalysisServices.Core.dll.
• See Analysis Services Engine (SSAS).
• SQL Server 2016 Integration Services (SSIS)
• AlwaysOn availability group support
• Incremental package distribution
• Always Encrypted support
• New role at the ssis_logreader database level
• New custom registration level
• Column names for errors in the data stream
• New connectors
• Support for the Hadoop file system (HDFS)
• See Integration Services (SSIS).
• SQL Server 2016 Master Data Services (MDS)
• Improvements to derived hierarchies, including support for recursive and many-to-many hierarchies
• Filtering of domain-based attributes
• Entity synchronization for sharing entity data between models
• Approval workflows through change sets
• Custom indexes to improve query performance
• New levels of authorization to improve security
• New design of the business rules management experience
• See Master Data Services (MDS).
• SQL Server 2016 Reporting Services (SSRS)
• Reporting Services has been completely renewed in this version.
• New web portal for reports with KPI functionality
• New Mobile Report Publisher
• Redesigned report rendering engine that supports HTML5
• New tree map and radial chart types