Description
Microsoft SQL Server 2016 Standard
- 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 link 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 columnstore 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.
- QL Server 2016 Analysis Services provides improved performance, authoring,
- database management, filtering, processing, and much more for tabular model
- databases based on the 1200 compatibility level.
- SQL Server R Services integrate the R programming language, used for statistical
- analysis, into SQL Server.
- New Database Consistency Checker (DBCC) runs internally to detect potential data
- corruption issues.
- Direct Query, which queries live external data rather than importing it first, now
- supports more data sources, including Azure SQL, Oracle and Teradata.
- There are numerous new DAX (Data Access Expressions) functions.
- New Microsoft.AnalysisServices.Tabular namespace manages tabular mode
- instances and models.
- Analysis Services Management Objects (AMO) is re-factored to include a second
- assembly, Microsoft.AnalysisServices.Core.dll.
- QL Server 2016 Integration Services (SSIS): Support for Always On Availability
- Groups
- Incremental package deployment
- Always Encrypted support
- New ssis_logreader database-level role
- New custom logging level
- Column names for errors in the data flow
- New connectors
- Support for the Hadoop file system (HDFS): SQL Server 2016 Master Data Services
- (MDS)
- Derived hierarchy improvements, including support for recursive and many-to-
- many hierarchies
- Domain-based attribute filtering
- Entity syncing for sharing entity data between models
- Approval workflows via changesets
- Custom indexes to improve query performance
- New permission levels for improved security
- Redesigned business rules management experience