SQL Server 2016: New Enhancements

Stretch Database

SQL Server 2016 will bring in a new concept called stretch database, where we have ability to pick a specific table and stretch it to Azure.If an application queries for the historical data, it will be retrieved from Azure.

Stretch Database is the capability to extend a database table into the Azure cloud platform. With this ability, Organizations don’t have to worry about purchasing extra storage space, or purge the old historical data.

 

JSON Support

To directly query JSON for scalar values, you can use the JSON_VALUE function. This function uses a JavaScript like notation to locate values within the JSON object. It uses the $ symbol as the object root, dot-notation for properties, and brackets for array indexes. The PostgreSQL equivalent is json_extract_path_text.

Sample Query:

query using JSON

SELECT name, database_id, source_database_id
FROM sys.databases
ORDER BY database_id
FOR JSON AUTO, INCLUDE_NULL_VALUES;

Output

[
{
“name”:”master”,
“database_id”:1,
“source_database_id”:null

},
{
“name”:”tempdb”,
“database_id”:2,
“source_database_id”:null
}

{
“name”:”msdb”,
“database_id”:3,
“source_database_id”:null
}

]

Incremental Package Deployment

With the help of this new Incremental Package Deployment feature we can deploy one or more package to the existing or new projects without deploying the whole project. We can incrementally deploy packages using: Deployment Wizard, SQL SERVER Management Studio (uses Deployment Wizard), stored procedures, and Management Object Model (MOM) API at this time.

The incremental process can be actioned from:

1.      Deployment Wizard.

2.      SQL Server Management Studio (SSMS).

3.      Stored Procedures.

4.      Management Object Model (MOM) API.

 

In-Memory OLTP

 In SQL Server 2016 Community Technology Preview 2 (CTP2), improvements to In-Memory OLTP enable scaling to larger databases and higher throughput in order to support bigger workloads. In addition, a number of limitations concerning tables and stored procedures have been removed to make it easier to migrate your applications to and leverage the benefits of In-Memory OLTP.

 

Here is the comparison between SQL Server 2014 & 2016

Feature/Limit	SQL Server 2014	SQL Server 2016
Maximum size of durable table	256 GB	2 TB
LOB (varbinary(max), [n]varchar(max))	Not supported	Supported*
Transparent Data Encryption (TDE)	Not supported	Supported
Offline Checkpoint Threads	1	1 per container
ALTER PROCEDURE / sp_recompile	Not supported	Supported (fully online)
Nested native procedure calls	Not supported	Supported
Natively-compiled scalar UDFs	Not supported	Supported
ALTER TABLE	Not supported (DROP / re-CREATE)	Partially supported (offline – details below)
DML triggers	Not supported	Partially supported (AFTER, natively compiled)
Indexes on NULLable columns	Not supported	Supported
Non-BIN2 collations in index key columns	Not supported	Supported
Non-Latin codepages for [var]char columns	Not supported	Supported
Non-BIN2 comparison / sorting in native modules	Not supported	Supported
Foreign Keys	Not supported	Supported
Check/Unique Constraints	Not supported	Supported
Parallelism	Not supported	Supported
OUTER JOIN, OR, NOT, UNION [ALL], DISTINCT, EXISTS, IN	Not supported	Supported
Multiple Active Result Sets (MARS) (Means better Entity Framework support.)	Not supported	Supported
SSMS Table Designer	Not supported	Supported

 

 

SQL Server 2012 SSIS Enhancements

 SQL Server 2012 Undo and Redo Features

For a package, undo /redo is available in the Control Flow, Data Flow, Event Handlers, and Parameters tabs, and in the Variables window. For a project, undo/redo is available for the project parameters.
You can’t undo/redo changes to the new SSIS Toolbox, and you can’t undo/redo shared connections.
To undo an action, click the undo button or press CTRL+Z. To redo an action, click the redo button or press CTRL + Y.

SQL Server Integration Services Toolbox Enhancements

 

In 2012, components inside the toolbox have been placed in few more categories (Favorites and Common) for ease of use.  These categories are not fixed and you can customize it as per your need. For example, you can place all your frequently used components inside "Favorites" section which appears at the top of the Toolbox as shown below:

 

To move a component to some other location/category, right click on the component and click on the location where you want that component to be placed as shown below. 

SQL Server Integration Services Solution Explorer Enhancements

SQL Server 2012 now supports a new project deployment model as well as the legacy deployment model. The Legacy Deployment Model lets you deploy each individual package separately whereas the new Project Deployment Model lets you create deployment packet (which is an *.ispac file) from the SSIS project. Each SSIS project which you create in SQL Server 2012, by default gets created in Project Deployment Model which you can change if needed as shown below:

 

By default, new SSIS projects get created in the Project Deployment Model. When we right click on the project we now have two more options. The "Project Parameters" let's you define the parameters in the project and the "Deploy" option let's you create the deployment package or deploy to the Integration Services Catalog database.

 

If you right click on the package in the Solution Explorer, you will notice one more option (Entry-Point Package). This option is helpful in the scenario when you have a parent package which calls child packages. You can set the Entry-Point Package option for the parent package.