Technical Overview: SQL Server 2016 Release Candidate 0
The SQL Server engineering team is pleased to announce the availability of SQL Server 2016 Release Candidate 0. This is an important milestone in the release of SQL Server 2016, as it marks feature completion for most dimensions of the product and means a very rich set of capabilities are now available. These include: 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.
To learn more about the release, visit the SQL Server 2016 preview page. To experience the new, exciting features in SQL Server 2016 and the new rapid release model, download the preview and start evaluating the impact these new innovations can have for your business.
Questions? Join the discussion of the new SQL Server 2016 capabilities at MSDN and StackOverflow. If you run into an issue or would like to make a suggestion, you can let us know using Microsoft’s Connect tool. We look forward to hearing from you.
For more information on this release, see SQL Server 2016 Release Notes and What’s New in SQL Server 2016.
Database scoped configuration
This release now supports a new database level object that holds optional configuration values that affect the behavior of the application code at the database level. This support is available in both SQL Server 2016 Release Candidate (RC0) and SQL Database V12 using the new ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) statement. 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.
The following T-SQL Syntax is supported:
ALTER DATABASE SCOPED CONFIGURATION
{
{ [ FOR SECONDARY] SET }
}
| CLEAR PROCEDURE_CACHE
[;]
< set_options > ::=
{
MAXDOP = { | PRIMARY}
| LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}
| PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
| QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
}
The new functionality is supported for both Azure SQL Database and SQL Server.
PolyBase enhancements in RC0 include:
- Support for the latest Cloudera distribution CDH5.5 on Linux
- Support for public containers and blobs in Azure blob storage
SQL Server Management Studio improvements in this release include:
In-Memory OLTP new features in RC0:
- Parallel scan of non-clustered indexes. All indexes on memory-optimized tables now support parallel scan, leading to increased performance of analytics-style queries that scan large sets of data.
- Reduced downtime during upgrade: upgrade from earlier builds of SQL Server 2016 no longer runs database recovery; the time it takes to run upgrade is no longer a factor of the data size. For upgrade and attach/restore from SQL Server 2014 the number of database restarts is reduced to one: during upgrade, the database is restarted once, thus the time it takes to upgrade is a factor of the data size.
- Built-in function @@SPID is now supported natively compiled T-SQL modules and in constraints on memory-optimized tables
- Log-optimized and parallel ALTER: most ALTER TABLE operations now run in parallel and result in only the metadata change to be written to the transaction log, reducing the time required to run the ALTER operation.
Improved support for large number of foreign key references
With CTP3.3, we added support for efficient DELETE operations for tables with up to 10,000 incoming foreign key references. With RC0, we are adding support for the following:
- Efficient UPDATE operations on tables with up to 10,000 incoming foreign key references.
- Efficient DELETE and UPDATE operations for partitioned tables with large numbers of foreign key references.
The following limitations still apply:
- MERGE operations are not supported for tables with large numbers of foreign key references
- Large numbers of foreign key references are not currently supported for memory-optimized tables, or stretch database.
New Built-in Table-Valued Function STRING_SPLIT
STRING_SPLIT is a T-SQL function that splits input character expression by specified separator and outputs result as a table.
Syntax:
STRING_SPLIT ( string, separator )
returns: table with a column named value
Examples:
Split comma separated value literal string
SELECT * FROM STRING_SPLIT(‘Lorem ipsum dolor sit amet.’, ”)
The result is:
value
—–
Lorem
ipsum
dolor
sit
amet.
Split comma separated value string in a column
SELECT id, title, value
FROM Article
CROSS APPLY STRING_SPLIT(tags, ‘,’)
Support for import and export of UTF-8 data
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 BULK INSERT T-SQL command and bcp command line utility. As a part of the import process, the UTF-8 encoded strings are read from the file, and converted and stored as native data types that correspond to the target columns in SQL Server tables. As a part of the export process, data are converted from native data types into UTF-8 encoded strings and written into the file. All native data types are supported except Xml, SqlVariant, Text, NText, and Image. The UTF-8 code page is specified by providing the following arguments:
- CODEPAGE = ‘65001’, DATAFILETYPE = ‘Char’, in case of BULK INSERT command,
- -c -C 65001, in case of bcp utility.
Examples:
Importing with BULK INSERT into SQL Server from a file
BULK INSERT MyTable
FROM ‘path\file.csv’
WITH ( FIELDTERMINATOR = ‘,’, CODEPAGE = ‘65001’, DATAFILETYPE = ‘Char’)
Importing with bcp utility into SQL Server from a file
bcp MyTable in “path\file.csv” -T -t , -c -C 65001
Exporting with bcp utility from SQL Server to a file
bcp MyTable out “path\file.csv” -T -t , -c -C 65001
AlwaysOn Availability Groups add support for:
- Distributed Availability Groups: It is now possible to configure replication between availability groups. Note: these different availability groups can live on different Windows Clusters.
- Streaming seeding of replicas: New secondary replicas in an Availability Group can now be seeded directly from the primary replica without the need for database backup/restore.
SQL Server Analysis Services (SSAS)
This month’s update to Analysis Services delivers support for display folders for Tabular models, any models created with new SQL Server 2016 compatibility level can now be used with PowerShell and SSIS. Finally, the new Tabular Object model is released to allow easier development and maintenance through code against tabular models. See the Analysis Services blog for more details.
SQL Server Reporting Services (SSRS)
Much more functionality is now available in this next preview of the new Reporting Services web portal. You can now:
- 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
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. Finally, the SSIS HDFS connector now supports the ORC file format, in addition to CSV and Avro. See the Integration Services blog for more details.
Master Data Services (MDS) adds a New Derived Hierarchy Management web page. See the Master Data Services blog for more details.
For more information on this release, see SQL Server 2016 Release Notes and What’s New in SQL Server 2016.
