SQL with Manoj

Around 3 years back (~ Nov’2010) the first Community Test Preview (CTP-1) of SQL Server 2012 was released, and with great excitement I blogged about its new features and availability. Now after 3 years, last month-end (June’2013) the first Community Test Preview (CTP-1) of SQL Server 2014 is released, and with same great excitement here I’m with my similar blog post.

SQL Server 2014, this time mainly focuses on high performance in-Memory Operations for OLTP Databases by providing Memory Optimized Tables & Natively Compiled Stored Procedures. There is not much enhancement in T-SQL section, which was with SQL Server 2012. But with In-Memory features SQL Server 2014 has made a lot of impression and is definitely going to make a lot of impact.

Let’s see what all new features SQL Server 2014 is packed with:

.
>> Database Engine:

–> Memory-optimized tables: read more.

–> Natively Compiled Stored Procedures: read more.

-> AlwaysOn enhancements
– 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.
– Enhancements are made to increase the efficiency and ease of troubleshooting availability groups
– Failover cluster instances (FCIs) can now use Cluster Shared Volumes as cluster shared disks in Windows Server 2012 and above.
– The following three dynamic management views now return information for FCIs:
1. sys.dm_hadr_cluster (Transact-SQL)
2. sys.dm_hadr_cluster_members (Transact-SQL)
3. sys.dm_hadr_cluster_networks (Transact-SQL)

–> Managing the lock priority of online operations
Additional partition switching and index rebuild operations can now be performed while a table is online. 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.

–> Columnstore indexes
– Updateable Clustered Columnstore Indexes: can perform some insert, update, and delete operations, read more.
– 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, read more.

–> 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.

–> Query plans
Includes substantial improvements to the component that creates and optimized query plans.

–> Inline specification of CLUSTERED and NONCLUSTERED
Inline specification of CLUSTERED and NONCLUSTERED indexes is now allowed for disk-based tables.

–> SELECT … INTO
The SELECT … INTO statement is improved and can now operate in parallel.

–> Deployment Enhancements
Deploy a SQL Server Database to a Windows Azure Virtual Machine enables deployment of a SQL Server database to a Windows Azure VM, read more.

.
>> Analysis Services & BI

1. Updates to Design Tool installation

2. Features recently added: Power View for Multidimensional Models

… read more.

.
>> No Changes to Integration Services, Reporting Services & Replication

.
You can know more about the CTP-1 version from my previous blog post. This post provides the Download Link, Install Prerequisites, Limitations, etc for the beta product.