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