Share via


What's New (Database Engine)

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.

Database Engine Feature Enhancements

Memory-Optimized Tables

In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine. In-Memory OLTP is optimized for OLTP. For more information, see In-Memory OLTP (In-Memory Optimization).

SQL Server Data Files in Azure

SQL Server Data Files in Azure enables native support for SQL Server database files stored as Azure Blobs. This feature allows you to create a database in SQL Server running in on-premises or in a virtual machine in Azure with a dedicated storage location for your data in Azure Blob Storage.

Host a SQL Server Database in an Azure Virtual Machine

Use the Deploy a SQL Server Database to an Azure Virtual Machine Wizard to host a database from an instance of SQL Server in an Azure Virtual Machine.

Backup and Restore Enhancements

SQL Server 2014 contains the following enhancements for SQL Server Backup and Restore:

  • SQL Server Backup to URL

    SQL Server Backup to URL was introduced in SQL Server 2012 SP1 CU2 supported only by Transact-SQL, PowerShell and SMO. In SQL Server 2014 you can use SQL Server Management Studio to backup to or restore from Azure Blob storage service. The new option is available both for the Backup task, and maintenance plans. For more information, see Using Backup Task in SQL Server Management Studio, SQL Server Backup to URL Using Maintenance Plan Wizard, and Restoring from Azure storage Using SQL Server Management Studio.

  • SQL Server Managed Backup to Azure

    Built on SQL Server Backup to URL, SQL Server Managed Backup to Microsoft Azure is a service that SQL Server provides to manage and schedule database and log backups. In this release only backup to Azure storage is supported. SQL Server Managed Backup to Microsoft Azure can be configured both at the database and at instance level allowing for both granular control at the database level and automating at the instance level. SQL Server Managed Backup to Microsoft Azure can be configured on SQL Server instances running on-premises and SQL Server instances running on Azure virtual machines. It is recommended for SQL Server instances running on Azure virtual machines. For more information, see SQL Server Managed Backup to Azure.

  • Encryption for Backups

    You can now choose to encrypt the backup file during a backup operation. It supports several encryption algorithms including AES 128, AES 192, AES 256, and Triple DES. You must use either a certificate or an asymmetric key to perform encryption during backup. For more information, see Backup Encryption.

New Design for Cardinality Estimation

The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator. For performance tuning and testing recommendations, see Cardinality Estimation (SQL Server).

Delayed Durability

SQL Server 2014 introduces the ability to reduce latency by designating some or all transactions as delayed durable. A delayed durable transaction returns control to the client before the transaction log record is written to disk. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.

For more information see the topic Control Transaction Durability.

AlwaysOn Enhancements

SQL Server 2014 contains the following enhancements for AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups:

Partition Switching and Indexing

The individual partitions of partitioned tables can now be rebuilt. For more information, see ALTER INDEX (Transact-SQL).

Managing the Lock Priority of Online Operations

The ONLINE = ON option now contains a WAIT_AT_LOW_PRIORITY option which permits you to specify how long the rebuild process should wait for the necessary locks. The WAIT_AT_LOW_PRIORITY option also allows you to configure the termination of blocking processes related to that rebuild statement. For more information, see ALTER TABLE (Transact-SQL) and ALTER INDEX (Transact-SQL). Troubleshooting information about new types of lock states is available in sys.dm_tran_locks (Transact-SQL) and sys.dm_os_wait_stats (Transact-SQL).

Columnstore Indexes

These new features are available for columnstore indexes:

  • Clustered columnstore indexes

    Use a clustered columnstore index to improve data compression and query performance for data warehousing workloads that primarily perform bulk loads and read-only queries. Since the clustered columnstore index is updateable, the workload can perform many insert, update, and delete operations. For more information, see Columnstore Indexes Described and Using Clustered Columnstore Indexes.

  • SHOWPLAN

    SHOWPLAN displays information about columnstore indexes. The EstimatedExecutionMode and ActualExecutionMode properties have two possible values: Batch or Row. The Storage property has two possible values: RowStore and ColumnStore.

  • Archival data compression

    ALTER INDEX ... REBUILD has a new COLUMNSTORE_ARCHIVE data compression option that further compresses the specified partitions of a columnstore index. Use this for archival, or for other situations that require a smaller data storage size and can afford more time for storage and retrieval. For more information, see ALTER INDEX (Transact-SQL).

Buffer Pool Extension

The Buffer Pool Extension provides the seamless integration of solid-state drives (SSD) as a nonvolatile random access memory (NvRAM) extension to the Database Engine buffer pool to significantly improve I/O throughput.

Incremental Statistics

CREATE STATISTICS and related statistic statements now permits per partition statistics to be created by using the INCREMENTAL option. Related statements allow or report incremental statistics. Affected syntax includes UPDATE STATISTICS, sp_createstats, CREATE INDEX, ALTER INDEX, ALTER DATABASE SET options, DATABASEPROPERTYEX, sys.databases, and sys.stats. For more information, see CREATE STATISTICS (Transact-SQL).

Resource Governor Enhancements for Physical IO Control

The Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within a resource pool. In SQL Server 2014, you can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to control the physical IOs issued for user threads for a given resource pool. For more information, see Resource Governor Resource Pool and CREATE RESOURCE POOL (Transact-SQL).

The MAX_OUTSTANDING_IO_PER_VOLUME setting of the ALTER RESOURCE GOVENOR sets the maximum outstanding I/O operations per disk volume. You can use this setting to tune IO resource governance to the IO characteristics of a disk volume and can be used to limit the number of IOs issued at the SQL Server instance boundary. For more information, see ALTER RESOURCE GOVERNOR (Transact-SQL).

Online Index Operation Event Class

The progress report for the online index operation event class now has two new data columns: PartitionId and PartitionNumber. For more information, see Progress Report: Online Index Operation Event Class.

Database Compatibility Level

The 90 compatibility level is not valid in SQL Server 2014. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL)

Transact-SQL Enhancements

Inline specification of CLUSTERED and NONCLUSTERED

Inline specification of CLUSTERED and NONCLUSTERED indexes is now allowed for disk-based tables. Creating a table with inline indexes is equivalent to issuing a create table followed by corresponding CREATE INDEX statements. Included columns and filter conditions are not supported with inline indexes.

SELECT ... INTO

The SELECT ... INTO statement is improved and can now operate in parallel. The database compatibility level must be at least 110.

Transact-SQL Enhancements for In-Memory OLTP

For information about the Transact-SQL changes to support In-Memory OLTP, see Transact-SQL Support for In-Memory OLTP.

System View Enhancements

sys.xml_indexes

sys.xml_indexes (Transact-SQL) has 3 new columns: xml_index_type, xml_index_type_description, and path_id.

sys.dm_exec_query_profiles

sys.dm_exec_query_profiles (Transact-SQL) monitors real time query progress while a query is in execution.

sys.column_store_row_groups

sys.column_store_row_groups (Transact-SQL) provides clustered columnstore index information on a per-segment basis to help the administrator make system management decisions.

sys.databases

sys.databases (Transact-SQL) has 3 new columns: is_auto_create_stats_incremental_on, is_query_store_on, and resource_pool_id.

System View Enhancements for In-Memory OLTP

For information about system view enhancements to support In-Memory OLTP, see System Views, Stored Procedures, DMVs and Wait Types for In-Memory OLTP.

Security Enhancements

CONNECT ANY DATABASE Permission

A new server level permission. Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. Does not grant any permission in any database beyond connect. Combine with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of SQL Server.

IMPERSONATE ANY LOGIN Permission

A new server level permission. When granted, allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases. When denied, a high privileged login can be blocked from impersonating other logins. For example, a login with CONTROL SERVER permission can be blocked from impersonating other logins.

SELECT ALL USER SECURABLES Permission

A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.

Deployment Enhancements

Azure VM

Deploy a SQL Server Database to a Microsoft Azure Virtual Machine enables deployment of a SQL Server database to an Azure VM.

ReFS

Deployment of databases on ReFS is now supported.

See Also

Features Supported by the Editions of SQL Server 2014