SQL with Manoj

Microsoft yesterday (29-Oct-2015) announced the CTP 3 update of the recently and initially released Community Technology Preview (CTP) 2.x version of SQL Server 2016.
 

[Register and Download the CTP 3 Evaluation version (180 days) here]
 

–> Direct download link (~2.3 GB):

– Or download both EXE & BOX files:
– – Box file SQLServer2016-x64-ENU.box
– – EXE file SQLServer2016-x64-ENU.exe
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2016 (CTP3) – 13.0.700.139 (X64) Oct 7 2015 04:48:43 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 10240: )
 

–> The Major feature in this preview is the “RRE Integration”:

That is the Advanced Revolution Analytics R Integration, as SQL Server R Service within SQL Server 2016. “R” is the most popular programming language for Advanced Analytics. You can use it to analyze data, uncover patterns and trends and build predictive models. It offers an incredibly rich set of packages and a vibrant and fast-growing developer community.

SQL Server R Services with in-database analytics will help with:

– Data Exploration and Predictive Modeling with R over SQL Server data

– Operationalizing your R code using T-SQL

While installing SQL Server 2016, you will get an option to integrate R, it mentions “Includes Advanced Analytics Extensions that enables integration with R language using standard T-SQL statements.”, as shown below:

Post installation you need to go thru these steps to configure “R” with SQL Server, [link].
 

–> Another major feature is “Transactional Replication from SQL Server to Azure SQL DB”

Now you can setup Azure SQL DB as a subscriber of transaction replication, allowing you to migrate data from SQL Server instance on-premises or in IaaS to Azure SQL database without downtime.
 

–> With this release SQL Server team has added and improved lot of new features. Some of the important Enhancements and Issues fixed in this release are:
 

1. In-Memory improvements: [read more]

– Support for FOREIGN KEY, CHECK and UNIQUE constraints.

– Inline table-values functions.

– Existing NonClustered ColumnStore indexes are updateable without requiring index rebuild.

– Parallel Index build for NonClustered ColumnStore indexes.

– Performance improvements (INSERT, String pushdown, bypassing delete buffer when processing deleted rows).
 

2. Stretch Database improvements: [read more]

– Create/Drop index support

– Improved credential management for remote Stretch database stores.

– Improved performance for joins between stretched tables.

– SSMS GUI updates:
– – Database and Table level fly out menu options.
– – Stretch monitor functionality added to allow users to monitor current migration status, including the ability to pause the migration at the table level.
– – Added ability to Stretch individual tables, and added option to Stretch to new SQL Azure or existing SQL Azure server.
 

3. JSON improvements: [read more]

– OPENJSON() Table value function, parses JSON text and returns rowset.

– JSON_VALUE() Scalar function, returns a value from JSON on the specified path.

– ISJSON() function, validates that JSON is properly formatted.

– JSON_QUERY function, returns a fragment from the JSON text.
 

4. Temporal improvements: [read more]

– Support for using Temporal with In-Memory OLTP. Combining disk-based table for cost-effective storing of history data with memory-optimized tables for storing latest (actual) data.

– Direct ALTER for system-versioned temporal tables.

– New clause FOR SYSTEM_TIME ALL for querying entire data history easily without specifying period boundaries.

5. Row-Level Security (RLS) with Block Predicates: [read more]

– Preventing users from Inserting, Updating, and/or Deleting rows that violate the predicate.

– Security policies can now be created with SCHEMABINDING = OFF.
 

5. Query Store improvements [read more]

– Performance monitoring supported for In-Memory OLTP workloads.
 

–> Integration Services (SSIS) improvements:

– SSIS control flow template enables customers to save a commonly used control flow task or container to a standalone template file and reuse it multiple times in a package or multiple packages in a project.

– Added Azure blob source support for the Import/Export wizard.

– Relaxed Max Buffer Size of Data Flow Task.
 

–> Analysis Services (SSAS) improvements:

– DBCC support

– SSMS and SSDT updates for Tabular

– Check more at SSAS team blog.
 

–> Reporting Services (SSRS) improvements:

– Pin Reporting Services report items.

– Check more at SSRS team blog.
 

I’ve covered very few and important points here, you can check all the updates here in [SQL Server blog for CTP 3] with details.
 

For all these new features released in SQL Server 2016, check my blog posts here.
 

So, download the Preview today and start playing with the new features and plan your DB migration/upgrade.
 

Check & Like my FB Page.