Technical Overview: SQL Server 2016 Community Technology Preview 3.3

The SQL Server engineering team is pleased to announce the immediate availability of SQL Server 2016 public preview release CTP 3.3. This release advances the “Cloud First” tenet; the build has already been deployed to SQL Azure Database worldwide.

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 or try the preview by using a virtual machine in Microsoft Azure 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.

New Stretch Database improvements in CTP 3.3 include:

  • Support to enable TDE on a remote DB if the local database has TDE enabled
  • Azure Stretch database edition preview with support for up to 60TB
  • Alter and drop index support for stretch tables
  • Add, alter and drop columns support for stretch tables
  • Point-in-time restore and geo-failover support
  • Query performance improvement

SQL Server Management Studio improvements in this release include:

  • Additional Wizard features:
    • Added new SQL db credential management functionality
    • Integrated Table validation and selection updates to prevent stretch of unsupported datatypes at selection time
    • Table search functionality for table select page
    • Table selection column reordering
    • Support for temporal tables during table select
    • Integrated Azure sign in and SQL sign in credential
    • Add support for stretching using federated accounts
    • New firewall configuration and subnet detection functionality
    • Updated summary page details with pricing information
    • Improved SSMS visualization with StretchDB icons
  • Object Explorer:
    • Fly out menu updates to support disable and un-migration functionality
    • Un-migrate support functionality at database and table level

Read the SSMS blog post to learn more.

CTP3.3 adds support with In-Memory OLTP for:

  • Automatic update of statistics on memory-optimized tables: The statistics for memory-optimized tables are now updated automatically, removing the need for running maintenance tasks that update statistics manually.
  • Sampled statistics for memory-optimized tables: Sampling of statistics for the data in memory-optimized tables is now supported, alongside the previously supported fullscan statistics. This reduces the time it takes to collect statistics for large tables.
  • Use of LOB types varchar(max), nvarchar(max), and varbinary(max) with built-in string functions (‘+’, len, ltrim, rtrim and substring) in natively compiled modules, and as return type of natively compiled scalar UDFs.
  • Memory-optimized tables with row size > 8060 bytes, using non-LOB types. CTPs 3.1 and 3.2 supported larger rows using LOB types; as of CTP3.3, memory-optimized tables support also larger rows using types varchar(n), nvarchar(n) and varbinary(n). See below for an example.
  • The OUTPUT clause can now be used with INSERT, UPDATE and DELETE statements in natively compiled stored procedures.

Autostats improvements in CTP 3.3

Previously, statistics were automatically recalculated when the change exceeded a fixed threshold. As of CTP 3.3, we have refined the algorithm such that it is no longer a fixed threshold, but in general will be more aggressive in triggering statistics scans, resulting in more accurate query plans.

Foreign Key Support enhancements in CTP 3.3

SQL Server 2014 and earlier versions have limitations on the number of FOREIGN KEY references a table can contain, as well as the maximum number on incoming foreign key REFERENCES. The documented recommended maximum is 253, and when performing DML on tables with large numbers of incoming REFERENCES, statements time out with stack overflow error messages.

This improvement increases the number of supported incoming foreign key REFERENCES to a table, while maintaining good performance for DML operations in both the referencing and the referenced table. The new maximum is 10,000. However, with the CTP 3.3 release, we have certain limitations on this feature:

  • We ONLY support Delete DML operation on foreign key references that go beyond the current recommended maximum of 253. Therefore, we will validate that no referencing rows exist before deletion.
  • Update and Merge operations are not supported with this release. Update will be available in RTM.
  • You will not see any change in behavior for cascading actions.
  • This is not available in ColumnStore, Hekaton or StretchDB.
  • This change is not applicable to a primary key table that is self-referencing (that is, if the table has a foreign key to itself). In this case, the behavior would remain the same as before.
  • This is not supported for partitioned foreign key tables for CTP 3.3. However, partitioned tables will be supported in RTM.

SQL Server Analysis Services (SSAS) includes multiple additions in this release. Read the SSAS CTP 3.3 blog post to learn more.

SQL Server Reporting Services (SSRS) includes an updated preview of its brand-new web portal with additional functionality:

  • Add the KPIs and reports that matter most to you to your Favorites and view them all in one place.
  • Manage shared data sources for your KPIs and reports and perform other management tasks.

Read the SSRS blog post to learn more.

Master Data Services (MDS) improvements in this release include:

  • Business rule changes
    • New, easier-to-use web UI administration page
    • Support for NOT conditional operator
    • Support for ELSE section that contains a set of actions to execute if the IF condition is false
    • Removed management UI from Excel add-in
  • Added support for purging (hard-deleting soft-deleted members) of an entity version
  • Added to the web explorer page a button to open the current entity view in the Excel add-in