SQL with Manoj

Microsoft (on 7-March-2015) announced the first SQL Server 2016 release candidate, SQL Server 2016 Release Candidate (RC) 0. With this release SQL Server adds rich set of capabilities, including Real-time Operational Analytics, rich Visualizations on mobile devices, built-in Advanced Analytics, new advanced Security technologies, and new Hybrid scenarios allowing you to securely Stretch data to the cloud.
 

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

–> Direct download link (~2.6 GB):

– Download the single ISO: SQLServer2016RC0-x64-ENU.iso

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

– Download SSMS: SSMS-Full-Setup.exe
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2016 (RC0) – 13.0.1100.288 (X64)
Feb 29 2016 23:19:56
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 10586: )
 

Please Note: Management Studio (SSMS) is no longer bundled with the ISO or CAB files, and will need to be installed separately, link mentioned above for RC0.
 

–> The RC 0 has some awesome new features:

1. Database scoped configuration: a new database level object that holds optional configuration values that affect the behavior of the application code at the database level. This statement modifies the default SQL Server 2016 Database Engine behavior for a particular database. A generic mechanism for creating database configuration(s) at creation time is not provided. These options are:
   – Clear procedure cache.
   – Set the MAXDOP parameter to an arbitrary value (1,2, …) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (e.g. for reporting queries).
   – Set the query optimizer cardinality estimation model independent of the database to compatibility level.
   – Enable or disable parameter sniffing at the database level.
   – Enable or disable query optimization hotfixes at the database level.

2. PolyBase enhancements includes support for:
   – The latest Cloudera distribution CDH5.5 on Linux.
   – Public containers and blobs in Azure blob storage.

3. In-Memory OLTP enhancements includes:
   – Parallel scan of non-clustered indexes.
   – Reduced downtime during upgrade.
   – Built-in function @@SPID is now supported natively compiled T-SQL modules and in constraints on memory-optimized tables
   – Log-optimized and parallel ALTER (ALTER TABLE operations now run in parallel).

4. STRING_SPLIT() and STRING_ESCAPE() function, new in-built Table-Valued Functions:
   – STRING_SPLIT, Splits input character expression by specified separator and outputs result as a table.
   – STRING_ESCAPE, Escapes special characters in texts and returns text with escaped characters.

5. Support for UTF-8 data to export and import.
   – Data stored in a UTF-8 encoded file can now be imported into SQL Server and exported from SQL Server into a UTF-8 encoded file, using BCP utility, BULK INSERT and OPENROWSET.

6. JSON_MODIFY() new function:
   – To update the value of a property in a JSON string and return the updated JSON string.

7. Stretch Database enhancements:
   – New version of Stretch Database on Azure that supports 60 TB of data
   – Azure Active Directory authentication, The option to use a federated service account for SQL Server to communicate with the remote Azure SQL Database server when certain conditions are true no longer requires a trace flag.

8. ColumnStore Index enhancements:
   – Compression delay. This new option minimizes the impact of the transactional workload on real-time operational analytics.
   – GROUP BY performance enhancement, aggregate pushdown for GROUP BY clause when run against a nonclustered columnstore index.
   – New alter table option, now you can create a columnstore index on an existing memory-optimized table.

9. SQL Server Management Tools (SSMS): SQL Server Management Tools is no longer installed from the main feature tree, as mentioned above.
 

–> Few more enhancements in other areas:

1. AlwaysOn Availability Groups add support for:
   – Distributed Availability Groups.
   – Streaming seeding of replicas.

2. SSAS (Analysis Services) add support for :
   – Display folders in Analysis Services.
   – PowerShell support for Tabular models.
   – SSIS support for Tabular models.
   – Tabular Object model for Tabular models.
   – more at Analysis Services blog.

3. SSRS (Reporting Services) support for:
   – Subscribe to reports to receive them in your email inbox, download, rename, move, and delete reports and other catalog items.
   – Manage shared datasets.
   – Manage data caching and refresh.
   – Create “linked” reports with different default parameter values.
   – Pin to a dashboard in a group when pinning a report chart, gauge, map, or image to a Power BI dashboard.
   – Choose the currency for monetary values when creating a KPI or mobile report.
   – more at Reporting Services blog.

4. SQL Server Integration Services (SSIS)
   – SSIS Projects in Visual Studio SQL Server Data Tools (SSDT) can now target 2012, 2014 and 2016 versions of SQL Server by switching a project level property between 2012, 2014 and 2016 SQL server.
   – SSIS now supports setting a server wide customized logging level.
   – The SSIS HDFS connector now supports the ORC file format, in addition to CSV and Avro.
   – more at Integration Services blog.

I’ve covered very few and important points here, you can check all the updates here in [SQL Server blog for RC 0] 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.
 

Do check & Like my FB Page.