In Previous post we have seen the new features of SQL server 2016 in short and had a brief discussion about  ALWAYS ENCRYPTED Feature. You can Find That Post Here .

SQL Server 2016 features to look for -1

Now In The Today’s Post we will have look on 2 new features

Stretch Database

Native support for JSON

Stretch Database

If We have got a ton of data but most of it just isn’t queried Often. so we tends to archive it. many thinks that in this case partitioning helps but . sadly it  doesn’t, if want to enhance the performance you have to make sure your WHERE clause specifically excludes the partition which is archived if not then Sql server will ends up scanning all the partitions.

SQL Server 2016 will bring in a new concept called stretch database, where in you have the 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. This ability to store warm and cold transaction data in Azure means that organizations don’t have to worry about having to purchase extra storage space, or being forced to permanently erase these old or infrequently used data.

Storage in Azure is cheap and the price will always go down as long as the “Cloud War” is happening between various cloud support provides like Amazon , Google,M/S .and there is no question for security. because concepts like Always Encrypted, the data which resides or in motion to Azure is secure.

But with This Microsoft has to play smarter As Per Brent Ozar

If Microsoft does not deliver better partition elimination than this feature will be LEGENDARILY BAD because scanning partitions up in Azure is going to be even worse than scanning partitions locally.

And the following questions still there

• How will backups work?
• How will restores work?
• How will DBCCs work?
• How will data be moved from on-premise to Azure?
• Can multiple servers attach to the Azure partitions?

Support for JSON

The item titled “Add native support for JSON to SQL Server, a la XML (as in, FOR JSON or FROM OPENJSON)” is the No. 1 requested feature on the Connect site used to garner feature requests for users of SQL Server and Windows Azure SQL Database.With more than 1,000 votes and leading other items by more than a 140 votes, the item posted more than four years ago reads:

“

While JSON import and export is possible in SQL Server using horribly complex T-SQL code or CLR integration using the JavaScript JSON methods, such methods are system-resource intensive. It would be nice if MS could integrate JSON into SQL Server the same ways they do XML: say, a FOR JSON clause, an OPENJSON statement, etc.

“

It may have taken a while, but Microsoft — as it’s increasingly doing on many fronts these days — has listened and responded to its customers.

JSON— standing for JavaScript Object Notation — uses text name/value pairs to represent data, serving as a data transmission technology– easier to read and less complicated than XML.

it will be represented by the existing NVARCHAR type, used for representing variable-length strings.

as per Microsoft — they have studied the issue and decided to go the NVARCHAR type for many reasons concerning issues such as migration, cross-feature compatibility and client-side support.

It is probably a good idea to use the new ISJSON function as a check constraint on your JSON-containing nVarChar columns. If you don’t, you risk data corruption from flawed client applications inserting unparsable strings.

Querying JSON

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.

Here is a sample & simple query

IN Normal Format

Query

SELECT TOP (2)   name, database_id, source_database_id

FROM sys.databases

ORDER BY database_id;

Output

name                database_id                      source_database_id

——                  ———–                            ——————

master                   1                                         NULL

tempdb                  2                                         NULL

query using JSON

SELECT TOP (2)  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

}

]

Microsoft’s plan for JSON doesn’t include everything developers want. Many believe that a dedicated JSONB style column would offer better performance. Others want support for patching JSON data instead of having to replace the document as a whole.

We will look Other feature in next post.

IF u like or want to say anything than please do it ..