SQL SERVER 2014 Database Engine 15 new features

1. Memory Optimization table
In-memory OLTP is a memory-optimized database engine that is integrated into the SQL Server engine.
OLTP in memory has been optimized for OLTP. There are many restrictions on the external applications of Transact-SQL supported by OLTP in the memory.

2. SQL Server data files in Windows Azure
SQL Server data files in Windows Azure provide local support for SQL Server database files stored as Windows Azure Blob.
This feature allows you to create a database on an SQL Server running on a local or Windows Azure Virtual Machine and store data in a dedicated storage location in Windows Azure Blob storage.

3. host the SQL Server database in the Windows Azure Virtual Machine
By deploying the SQL Server database to the Windows Azure Virtual Machine wizard, you can host the database from the SQL Server instance to the Windows Azure virtual machine.

4. Enhanced Backup and restoration Functions
SQL Server 2014 provides the following enhancements for SQL Server backup and Restoration:
1) Back up SQL Server to URL
The SQL Server URL backup function is introduced in SQL Server 2012 SP1 CU2. Only Transact-SQL, PowerShell, and SMO support this function.
In SQL Server 2014, you can use SQL Server Management Studio to back up data to or restore data from the Windows Azure Blob storage service. This option can be used for backup tasks and maintenance plans.
2) SQL Server-managed backup to Windows Azure
Managed backup on SQL Server to Windows Azure is a service built based on the SQL Server backup to URL function. SQL Server provides this service to manage and arrange database and log backup. In SQL SERVER 2014, only Windows Azure storage can be backed up. SQL Server-managed backup to Windows Azure can be configured at the database and instance level at the same time, so as to implement database-level fine-grained control and instance-level automation. Managed backups of SQL Server to Windows Azure can be configured either on a local SQL Server instance or on an SQL Server instance running on a Windows Azure virtual machine. We recommend that you use this service for SQL Server instances running on Windows Azure virtual machines.
3) backup Encryption
You can choose to encrypt the backup file during the backup process. Currently, the supported encryption algorithms include AES 128, AES 192, AES 256, and Triple DES. Certificates or asymmetric keys must be used for encryption during Backup.

5. New designs for base Estimation
The base estimation logic, called the base estimator, has been redesigned in SQL Server 2014 to improve the quality of the query plan and therefore improve the query performance. The new base estimator is incorporated into the assumptions and algorithms that provide excellent performance in the new OLTP and data warehouse workloads.

6. latency persistence
SQL Server 2014 can shorten Latency by specifying partial or all transactions as delayed persistent transactions. A delayed persistent Transaction Returns control to the client before the transaction log is written to the disk. Continuity can be controlled at the database level, commit level, or atomic block level.

7. AlwaysOn Enhancement
SQL Server 2014 includes the following enhancements for AlwaysOn failover cluster instances and AlwaysOn availability groups:
1) The add Azure copy wizard simplifies the creation of hybrid solutions for AlwaysOn availability groups.
2) the maximum number of secondary replicas increases from 4 to 8.
3) When you disconnect from the primary copy, or when the cluster is not under arbitration, the readable secondary copy can now be used to read the workload.
4) The failover cluster instance (FCI) can now use the cluster shared volume (CSV) as the cluster shared disk.
5) provides a new system function sys. fn_hadr_is_primary_replica and a new DMV sys. dm_io_cluster_valid_path_names.
6) The following DMV has been enhanced and the FCI information is returned: sys. dm_hadr_cluster, sys. dm_hadr_cluster_members, and sys. dm_hadr_cluster_networks.

8. Partition switching and index generation
SQL SERVER 2014 can generate separate partitions for partitioned tables.

9. Manage the lock priority of online operations
The ONLINE = ON option now contains the WAIT_AT_LOW_PRIORITY option, which allows you to specify how long the regenerate process will wait for the required lock. The WAIT_AT_LOW_PRIORITY option also allows you to configure the termination of the blocking process related to the regenerate statement. The sys. dm_tran_locks (Transact-SQL) and sys. dm_ OS _wait_stats (Transact-SQL) provide troubleshooting information related to the new lock status type.

10. Column storage Index
These new features are available for column store indexes:
1) clustered column storage Index
Using clustered column storage indexes can improve the data compression and query performance of the Data Warehouse workloads that primarily perform large-capacity loading and read-only queries. Because clustered column storage indexes are updatable, the workload can perform many insert, update, and delete operations.
2) SHOWPLAN
SHOWPLAN displays information about the column storage index. The EstimatedExecutionMode and ActualExecutionMode attributes have two possible values: Batch or Row. The Storage attribute has two possible values: RowStore and ColumnStore.
3) archive data compression
Alter index... REBUILD provides the new COLUMNSTORE_ARCHIVE Data Compression option to further compress the specified partition of the column storage index. This can be used for archiving, or for other scenarios where smaller data storage sizes are required and more time can be spent on storage and retrieval.


11. Buffer Pool Expansion
Buffer Pool expansion provides seamless integration of Solid State disks (SSDs) to serve as a non-volatile random access memory (NvRAM) Expansion of the Database Engine Buffer Pool, significantly improving I/O throughput. SSD is really expensive.

12. incremental statistics
The create statistics and related STATISTICS statements now allow you to use the INCREMENTAL option to create statistics by partition.

13. enhanced physical I/O-controlled resource Governor Functions
Resource governor allows you to specify the limits on the CPU, physical I/O, and memory usage that can be used in the resource pool for incoming application requests. In SQL Server 2014, you can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to control the number of physical IO requests sent from a given resource pool to user threads. For more information, see RESOURCE Governor resource pool and create resource pool (Transact-SQL ).
The MAX_OUTSTANDING_IO_PER_VOLUME setting of alter resource govenor allows you to set the maximum I/O operations (IOPS) to be determined for each disk volume ). You can use this setting to adjust the IO resource control based on the IO characteristics of a disk volume, and it can be used to limit the number of IO requests sent at the SQL Server instance boundary.

14. Online Index Operation event class
The progress report for the online index operation event class now has two new data columns: PartitionId and PartitionNumber.

15. Database Compatibility Level
90 compatibility level is invalid in SQL Server 2014.