What's New in Database Engine
Updated: July 27, 2015
Applies To: SQL Server 2016 Preview
This latest release of the SQL Server Database Engine introduces new features and enhancements that increase the power and productivity of architects, developers, and administrators who design, develop, and maintain data storage systems. These are the areas in which the Database Engine has been enhanced.
This topic has the following sections.
[CTP 2.0] This release offers several new improvements for columnstore indexes including updateable nonclustered columnstore indexes, columnstore indexes on in-memory tables, and many more new features for operational analytics.
See these topics:
[CTP 2.0] In SQL Server 2016 Community Technology Preview 2 (CTP2), improvements to In-Memory OLTP enable scaling to larger databases and higher throughput in order to support bigger workloads. In addition, a number of limitations concerning tables and stored procedures have been removed to make it easier to migrate your applications to and leverage the benefits of In-Memory OLTP.
Do you want to try out SQL Server 2016 CTP2? Sign up for Microsoft Azure, and then go Here to spin up a Virtual Machine with SQL Server 2016 CTP2 already installed. You can delete the Virtual Machine when you’re finished.
The following table outlines these new features.
Support for performing ALTER operations for memory-optimized tables and natively compiled stored procedures.
Use MARS (Multiple Active Result Set) connections to access memory-optimized tables and natively compiled stored procedures
This allows leveraging In-Memory OLTP in existing applications that rely on MARS.
Support for natively compiled, scalar user-defined functions.
Complete support for collations
Enhancements to transaction performance analysis reports
Support for nesting of natively compiled stored procedures, and other increases in the Transact-SQL surface area.
Support for subqueries in natively compiled stored procedures.
PowerShell cmdlet for evaluating the migration fitness of multiple objects in a SQL Server database.
For more information, see In-Memory OLTP (In-Memory Optimization).
[CTP 2.0] Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another. For more information, see Live Query Statistics.
[CTP 2.0] Query store is a new feature that provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows, allowing you to see database usage patterns and understand when query plan changes happened on the server. The query store presents information by using a Management Studio dialog box, and lets you force the query to one of the selected query plans. For more information, see Monitoring Performance By Using the Query Store.
[CTP 2.0] A temporal table is a new type of table that provides correct information about stored facts at any point in time. Each temporal table consists of two tables actually, one for the current data and one for the historical data. The system ensures that when the data changes in the table with the current data the previous values are stored in the historical table. Querying constructs are provided to hide this complexity from users. For more information, see Temporal Tables.
As of SQL Server 2016 CTP 2.1 users will be able to mark one or both period columns with the HIDDEN flag.
[CTP 2.0] SQL Server backup to URL using the Microsoft Azure Blob storage service now supports using block blobs instead of page blobs. Block blobs have a size limitation of 200GB per blob, whereas page blobs have a size limitation per blob of 1 TB. With block blobs, you can stripe your backup set across multiple blobs resulting in a maximum backup size is 12.8 TB. Backup to page blobs does not support striping. In addition, block blobs have superior performance and a lower cost. As a result, striped backups for large databases can experience significantly decreased backup and restore times from Microsoft Azure.
There are two steps required to stripe your backup set to block blobs. First, you must obtain a Shared Access Signature (SAS) token to a container in your Azure Storage account. This SAS token is used to create a SQL Credential and then this SAS token is used when the Azure container is specified as the backup device. One way to obtain this SAS token is to use Azure PowerShell commands. Then you use the same Transact-SQL or PowerShell backup commands without specifying the WITH CREDENTIAL clause that was formerly required when using backup to URL. For examples, see Code Examples.
For SQL Server 2016 Community Technology Preview 2 (CTP2), the new backup to URL functionality does not yet have corresponding UI support in SQL Server Management Studio.
[CTP 2.0] SQL Server backup to URL now supports using Azure snapshots to backup databases in which all database files are stored using the Microsoft Azure Blob storage service. For more information, see Snapshot Backups for Database Files in Azure.
[CTP 2.0] In SQL Server 2016 Community Technology Preview 2 (CTP2) SQL Server Managed Backup to Microsoft Azure uses the new block blob storage for backup files. There are also several changes and enhancements to Managed Backup.
Support for both automated and custom scheduling of backups.
Support backups for system databases.
Support for databases that are using the Simple recovery model.
For more information, see SQL Server Managed Backup to Microsoft Azure
For SQL Server 2016 Community Technology Preview 2 (CTP2), these new managed backup features do not yet have corresponding UI support in SQL Server Management Studio.
[CTP 2.0] In general, you do not need to use trace flag 4199 in SQL Server 2016 Community Technology Preview 2 (CTP2) since most of the query optimizer behaviors controlled by this trace flag are enabled unconditionally under the latest compativility level (130) in SQL Server 2016 Community Technology Preview 2 (CTP2).
[CTP 2.0] Setup adds multiple tempdb data files during the installation of a new instance. Below are the pertinent details to consider:
By default, setup adds as many tempdb files as the CPU count or 8, whichever is lower.
You can configure the number of tempdb database files using the new command line parameter- /SQLTEMPDBFILECOUNT. It can be used for unattended and interactive installations.
setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS /FEATURES="SqlEngine" /INSTANCENAME="SQL15" .. /SQLTEMPDBDIR="D:\tempdb" /SQLTEMPDBFILECOUNT="4"
SQLTEMPDBFILECOUNT only accepts an integer value. If the parameter is not given or the value <= 0, setup will use the default value that is the number of (logical) cores on the machine or 8, whichever is lower. If the value is greater than the maximum allowed (cpu count), the installation will fail with an error.
The SQLTEMPDBFILECOUNT parameter is supported only in the following scenarios or actions: Install, InstallFailoverCluster, CompleteImage (sysprep), CompleteFailoverCluster (sysprep), and RebuildDatabase.
You can configure the number of tempdb database files using the new UI input control on the Database Engine Configuration section.
The primary database file tempdb will still be tempdb.mdf. The additional tempdb files are named as tempdb_mssql_#.ndf where # where # represents a unique number for each additional tempdb database file created during setup. The purpose of this naming convention is to make them unique. Uninstalling an instance of SQL Server deletes the files with naming convention tempdb_mssql_#.ndf. Do not use tempdb_mssql_*.ndf naming convention for user database files.
RebuildDatabase scenario deletes system databases and installs them again in clean state. Because the setting of tempdb file count does not persist, the value of number of tempdb files is not known during setup. Therefore, RebuildDatabase scenario does not know the count of tempdb files to be re-added. You can provide the value of the number of tempdb files again with the SQLTEMPDBFILECOUNT parameter. If the parameter is not provided, RebuildDatabase will add a default number of tempdb files, which is as many tempdb files as the CPU count or 8, whichever is lower.
[CTP 2.0] Format query results as JSON by adding the FOR JSON clause to a SELECT statement. Use the FOR JSON clause, for example, to delegate the formatting of JSON output from your client applications to SQL Server. Here's a sample query that uses the FOR JSON clause.
For more info, see Format Query Results as JSON with FOR JSON (SQL Server).
[CTP 2.0] With Always Encrypted, SQL Server can perform operations on encrypted data, and best of all the encryption key resides with the application inside the customer’s trusted environment and not on the server. Always Encrypted secures customer data so DBAs do not have access to plain text data. Encryption and decryption of data happens transparently at the driver level minimizing changes that have to be made to existing applications. For more information, see Always Encrypted (Database Engine).
[CTP 2.0] Stretch Database is a new feature in SQL Server 2016 Community Technology Preview 2 (CTP2) that leverages resources in Windows Azure to store and query archival data. Stretch Database automatically archives eligible rows from Stretch-enabled tables and uses computational resources in Azure to offload queries over the archived rows. For more info, see Stretch Database.
[CTP 2.2] You can now add security policies to a table that has been configured for Stretch Database. You can also configure a table that uses Row-Level Security for Stretch Database.
[CTP 2.2] Stretch Database Advisor is now available as a component of SQL Server 2016 Upgrade Advisor Preview 1. You can download Upgrade Advisor Preview 1 here or you can install it by using the Web Platform Installer. Stretch Database Advisor helps you to adopt Stretch Database by analyzing existing database tables based on adjustable table size thresholds to identify candidates for Stretch Database. For more info, see Identify databases and tables for Stretch Database.
[CTP 2.0] Numerous enhancements support the features described in the other sections of this topic. The following additional enhancements are available.
The TRUNCATE TABLE statement now permits the truncation of specified partitions. For more information, see TRUNCATE TABLE (Transact-SQL).
ALTER TABLE (Transact-SQL) now allows many alter column actions to be performed while the table remains available.
The full-text index DMV sys.dm_fts_index_keywords_position_by_document (Transact-SQL) returns the location of keywords in documents. This DMV has also been added in SQL Server 2012 SP2 and SQL Server 2014 SP1.
A new query hint NO_PERFORMANCE_SPOOL can prevent a spool operator from being added to query plans. This can improve performance when many concurrent queries are running with spool operations. For more information, see Query Hints (Transact-SQL).
Seven new views support the Query Store feature. For more information, see Query Store Catalog Views (Transact-SQL).
24 new columns are added to sys.dm_exec_query_stats (Transact-SQL) provide information about memory grants.
Two new query hints (MIN_GRANT_PERCENT and MAX_GRANT_PERCENT) are added to specify memory grants. See Query Hints (Transact-SQL).
[CTP 2.0] Row level security introduces predicate based access control. It features a flexible, centralized, predicate-based evaluation that can take into consideration metadata (such as labels) or any other criteria the administrator determines as appropriate. The predicate is used as a criterion to determine whether or not the user has the appropriate access to the data based on user attributes. Label based access control can be implemented by using predicate based access control. For more information, see Row-Level Security.
[CTP 2.0] Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. For more information, see Dynamic Data Masking.
[CTP 2.0] The ALTER ANY SECURITY POLICY permission is available as part of the implementation of row level security.
The ALTER ANY MASK and UNMASK permissions are available as part of the implementation of dynamic data masking.
The ALTER ANY COLUMN ENCRYPTION KEY, VIEW ANY COLUMN ENCRYPTION KEY, ALTER ANY COLUMN MASTER KEY DEFINITION, and VIEW ANY COLUMN MASTER KEY DEFINITION permissions are available as part of the implementation of the Always Encrypted feature.
The ALTER ANY EXTERNAL DATA SOURCE and ALTER ANY EXTERNAL FILE FORMAT permissions are visible in SQL Server 2016 Community Technology Preview 2 (CTP2) but only apply to the Analytics Platform System (SQL Data Warehouse).
[CTP 2.0] Load-balancing of read-intent connection requests is now supported across a set of read-only replicas. The previous behavior always directed connections to the first available read-only replica in the routing list. For more information, see Configure load-balancing across read-only replicas.
The number of replicas that support automatic failover has been increased from two to three.
Group Managed Service Accounts are now supported for AlwaysOn Failover Clusters. For more information, see Group Managed Service Accounts. For Windows Server 2012 R2, an update is required to avoid temporary downtime after a password change. To obtain the update, see gMSA-based services can't log on after a password change in a Windows Server 2012 R2 domain.
AlwaysOn Availability Groups supports distributed transactions and the DTC on Windows Server 2016. For more information, see SQL Server 2016 Support for DTC and AlwaysOn Availability Groups.
You can now configure AlwaysOn Availability Groups to failover when a database goes offline. This change requires the setting the DB_FAILOVER option to ON in the CREATE AVAILABILITY GROUP (Transact-SQL) or ALTER AVAILABILITY GROUP (Transact-SQL) statements.
[CTP 2.0] SQL Server Management Studio supports the Active Directory Authentication Library (ADAL) which is under development for connecting to Microsoft Azure. This replaces the certificate-based authentication used in SQL Server 2014 Management Studio.
SQL Server Management Studio installation requires installing .NET 4.6 as a pre-requisite. .NET 4.6 will be automatically installed by setup when SQL Server Management Studio is selected as an installation choice.
[CTP 2.2] SQL Server 2016 Upgrade Advisor Preview 1 is a standalone tool that enables users of prior versions to run a set of upgrade rules against their SQL Server database to pinpoint breaking and behavior changes and deprecated features as well as providing help with the adoption of new features such as Stretch Database.
You can download Upgrade Advisor Preview 1 here or you can install it by using the Web Platform Installer.