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;
FROM sys.databases
ORDER BY database_id
FOR JSON AUTO, INCLUDE_NULL_VALUES;
Output
[
{
“name”:”master”,
“database_id”:1,
“source_database_id”:null
{
“name”:”master”,
“database_id”:1,
“source_database_id”:null
},
{
“name”:”tempdb”,
“database_id”:2,
“source_database_id”:null
}
{
“name”:”tempdb”,
“database_id”:2,
“source_database_id”:null
}
{
“name”:”msdb”,
“database_id”:3,
“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 |
