What's new in SQL Server 2016
APPLIES TO: SQL Server (starting with 2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
With SQL Server 2016, you can build intelligent, mission-critical applications using a scalable, hybrid database platform that has everything built in, from in-memory performance and advanced security to in-database analytics. The SQL Server 2016 release adds new security features, querying capabilities, Hadoop and cloud integration, R analytics and more, along with numerous improvements and enhancements.
This page provides summary information and links to more detailed SQL Server 2016 what's new information for each SQL Server component.
Try SQL Server today!
- Download the free SQL Server 2016 Developer edition!.
- Download the latest version of SQL Server Management Studio (SSMS).
- Have an Azure account? Spin up a Virtual Machine with SQL Server 2016 already installed.
SQL Server 2016 Database Engine
- 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.
See Database Engine.
SQL Server 2016 Analysis Services (SSAS)
SQL 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.
SQL 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
SQL Server 2016 Reporting Services (SSRS)
Microsoft has thoroughly revamped Reporting Services in this release.
- New web Report Portal with KPI feature
- New Mobile Report Publisher
- Redesigned report rendering engine that supports HTML5
- New treemap and sunburst chart types
- SQL Server setup
- SQL Server 2016 Release Notes
- SQL Server 2016 datasheet
- Features supported by Editions of SQL Server
- Hardware and Software Requirements for Installing SQL Server 2016
- Install SQL Server 2016 from the Installation Wizard
- Setup and Servicing Installation
- New SQL PowerShell module
- UserVoice - Suggestion to improve SQL Server?
- Setup and Upgrade - MSDN Forum
- SQL Server Data Tools - MSDN forum
- Transact-SQL - MSDN forum
- SQL Server Security - MSDN Forum
- DBA Stack Exchange (tag sql-server) - ask SQL Server questions
- Stack Overflow (tag sql-server) - also has some answers about SQL development
- Reddit - general discussion about SQL Server
- Microsoft SQL Server License Terms and Information
- Support options for business users
- Contact Microsoft
- All the ways to get help
Contribute SQL documentation
Did you know that you could edit the content yourself? If you do so, not only will our documentation improve, but you'll also be credited as a contributor to the page.