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:
An Add Azure Replica Wizard simplifies creating hybrid solutions for AlwaysOn availability groups. For more information, see Use the Add Azure Replica Wizard (SQL Server).
The maximum number of secondary replicas is increased from 4 to 8.
When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
Failover cluster instances (FCIs) can now use Cluster Shared Volumes (CSVs) as cluster shared disks. For more information, see Always On Failover Cluster Instances.
A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.
The following DMVs were enhanced and now return FCI information: sys.dm_hadr_cluster, sys.dm_hadr_cluster_members, and sys.dm_hadr_cluster_networks.
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.