Skip to main content

What's new in SQL Server 2016? | 2016, 2017 and 2018

What Is the difference between SQL 2012 and 2016?

Top 7 Features Coming to SQL Server 2016, let's see in the detail with examples.

1.    Query Store
2.    Polybase
3.    Stretch Database
4.    JSON Support
5.    Row Level Security
6.    Always Encrypted
7.    In-Memory Enhancements

Query Store

Microsoft maintains query store upgrading most of all versions but What happens in this version?

Actually, In this version Microsoft trying to maintains a history of query execution plans with query performance and quickly queries etc.









PolyBase

Microsoft is introduced Polybase,  This is a data processing technique that is called SQL Server connector.

This SQL connector provides the connectivity to Azure Blob Storage and Hadoop using database tables query and Its dealing to a lot of large text files.






Stretch Database


In this section, Microsoft is trying to reduce your storage cast by a hybrid feature that is called Stretch Database.









JSON Support

JSON Support Is very awesome features (https://smallseotools.com/json-formatter/),  Using these features you can direct querying to Hadoop, SQL Server 2016 and also support to Lingua Franca.

Row Level Security

The SQL Server 2016 provide row-level security. It's very useful for multi-tenant environments and Its provide the limit to access the data based on role etc.


Always Encrypted

The SQL Server 2016 has the feature to supported both column level encryption and encryption in transit as well. 
The Always The encrypted mechanism provided an easy way to encryption to data and makes much better security.





In-Memory Enhancements

This feature already introduce in SQL Server 2014 but it has some limitation over data and issues like no-locking-issues and high-volume-session state issues.

Now in SQL Server 2016, Fixed the issues and trying to improved memory mgmt and Its also supporting foreign keys, check and unique constraints and parallelism also.

Insert multiple level JSON data into SQL Server 2016 -
Now Native JSON support in SQL Server 2016 and it provides you some functions to read and parse your JSON object to table format.

1.      The OPENJSON () table value function transforms JSON object to one or many rows. It will not execute any command. It just returns a table row if JSON text is properly formatted. OPENJSON function will also work with JSON arrays and this function can also open nested/hierarchical JSON objects. OPENJSON will just return set of rows instead of a single row.
2.      The JSON_Value () is a scalar function and used to returns a value from JSON on the specified path.

 There are some specific examples for OPENJSON read nested JSON –

Example 1 – OPENJSON AND JSON INPUT

DECLARE @json NVARCHAR(MAX)
SET @json=N'{
            "Name":"Anil",
            "Surname":"Singh",
            "Age":32,
            "Skills":["SQL","C#","MVC","Angular 2, 4 and 5"]
}'
SELECT * FROM OPENJSON(@json);
Result –
Example 2 – OPENJSON AND JSON INPUT

DECLARE @json NVARCHAR(MAX)
set @json =N'[
    {
        "CREATE_DT": 3443434343,
        "INCLUDEIND_IND": true,
        "CREATE_BY": "admin",
        "DOMAIN_ID": "Cable Length",
        "UPDATE_BY": null,
        "VISIBLE": true,
        "SOURCE": "OSB",
        "MIN_VALUE": 2,
        "UPDATE_DT": null,
        "VERSION": 1,
        "MAX_VALUE": 10
    },
    {
        "CREATE_DT": 34334343433,
        "INCLUDEIND_IND": true,
        "CREATE_BY": "admin",
        "DOMAIN_ID": "Number",
        "UPDATE_BY": null,
        "VISIBLE": true,
        "SOURCE": "COB",
        "MIN_VALUE": 1,
        "UPDATE_DT": null,
        "VERSION": 1,
        "MAX_VALUE": 10
    },
    {
        "CREATE_DT": 3434343,
        "INCLUDEIND_IND": true,
        "CREATE_BY": "admin",
        "DOMAIN_ID": "Number_concurrent_access",
        "UPDATE_BY": null,
        "VISIBLE": true,
        "SOURCE": "OCB",
        "MIN_VALUE": 1,
        "UPDATE_DT": null,
        "VERSION": 1,
        "MAX_VALUE": 5
    }]'
 CREATE TABLE TBL_ConvertJSONToTableObject(
             CREATE_DT VARCHAR(30),
             INCLUDEIND_IND VARCHAR(30),
             CREATE_BY VARCHAR(30),
             DOMAIN_ID VARCHAR(30) ,
             UPDATE_BY VARCHAR(30) ,
             VISIBLE VARCHAR(30)   ,
             SOURCE VARCHAR(30)             ,
             MIN_VALUE VARCHAR(30) ,
             UPDATE_DT VARCHAR(30) ,
             VERSION VARCHAR(30)   ,
             MAX_VALUE VARCHAR(30) ,
 )
 INSERT INTO TBL_ConvertJSONToTableObject (
             CREATE_DT,
             INCLUDEIND_IND,
             CREATE_BY,
             DOMAIN_ID,
             UPDATE_BY,
             VISIBLE ,
             SOURCE ,
             MIN_VALUE,
             UPDATE_DT,
             VERSION ,
             MAX_VALUE
 )
SELECT * FROM   OPENJSON (@json) 
            WITH(
                        CREATE_DT VARCHAR(30) '$.CREATE_DT',
                        INCLUDEIND_IND VARCHAR(30) '$.INCLUDEIND_IND',
                        CREATE_BY VARCHAR(30) '$.CREATE_BY',
                        DOMAIN_ID VARCHAR(30) '$.DOMAIN_ID',
                        UPDATE_BY VARCHAR(30) '$.UPDATE_BY',
                        VISIBLE VARCHAR(30) '$.VISIBLE',
                        SOURCE VARCHAR(30) '$.SOURCE',
                        MIN_VALUE VARCHAR(30) '$.MIN_VALUE',
                        UPDATE_DT VARCHAR(30) '$.UPDATE_DT',
                        VERSION VARCHAR(30) '$.VERSION',
                        MAX_VALUE VARCHAR(30) '$.MAX_VALUE'
)
SELECT * FROM TBL_ConvertJSONToTableObject
Result –
Example 3 –  USING JSON_VALUE() WITH OPENJSON() FUNCTION

DECLARE @json NVARCHAR(1000)
SELECT @json = N'{
            "Orders": [
                        {
                          "OrderID": 10100,
                          "CustomerID": 202000,
                          "OrderDetail": [{
                                      "ProductID": 302000,
                                      "UnitPrice": 4350     
                                    },     
                                    {            
                                      "ProductID": 203000,
                                      "UnitPrice": 4450
                                    },
                                    {            
                                      "ProductID": 43000,
                                      "UnitPrice": 5560
                                    }]
                        }]
}'
SELECT  JSON_Value (c.value, '$.OrderID') as OrderID,
    JSON_Value (c.value, '$.CustomerID') as CustomerID,
    JSON_Value (p.value, '$.ProductID') as ProductID,
    JSON_Value (p.value, '$.UnitPrice') as UnitPrice
FROM OPENJSON (@json, '$.Orders') as c
                                CROSS APPLY OPENJSON (c.value, '$.OrderDetail') as p
Result –

Now you can download SQL Server 2016 from given below link
http://www.microsoft.com/en-in/server-cloud/products/sql-server-2016/

Labels

Labels:
By Anil Singh | Rating of this article (*****)

Popular posts from this blog

List of Countries, Nationalities and their Code In Excel File

Download JSON file for this List - Click on JSON file    Countries List, Nationalities and Code Excel ID Country Country Code Nationality Person 1 UNITED KINGDOM GB British a Briton 2 ARGENTINA AR Argentinian an Argentinian 3 AUSTRALIA AU Australian an Australian 4 BAHAMAS BS Bahamian a Bahamian 5 BELGIUM BE Belgian a Belgian 6 BRAZIL BR Brazilian a Brazilian 7 CANADA CA Canadian a Canadian 8 CHINA CN Chinese a Chinese 9 COLOMBIA CO Colombian a Colombian 10 CUBA CU Cuban a Cuban 11 DOMINICAN REPUBLIC DO Dominican a Dominican 12 ECUADOR EC Ecuadorean an Ecuadorean 13 EL SALVA...
Read more

39 Best Object Oriented JavaScript Interview Questions and Answers

Most Popular 37 Key Questions for JavaScript Interviews. What is Object in JavaScript? What is the Prototype object in JavaScript and how it is used? What is "this"? What is its value? Explain why "self" is needed instead of "this". What is a Closure and why are they so useful to us? Explain how to write class methods vs. instance methods. Can you explain the difference between == and ===? Can you explain the difference between call and apply? Explain why Asynchronous code is important in JavaScript? Can you please tell me a story about JavaScript performance problems? Tell me your JavaScript Naming Convention? How do you define a class and its constructor? What is Hoisted in JavaScript? What is function overloadin...
Read more

nullinjectorerror no provider for httpclient angular 17

In Angular 17 where the standalone true option is set by default, the app.config.ts file is generated in src/app/ and provideHttpClient(). We can be added to the list of providers in app.config.ts Step 1:   To provide HttpClient in a standalone app we could do this in the app.config.ts file, app.config.ts: import { ApplicationConfig } from '@angular/core'; import { provideRouter } from '@angular/router'; import { routes } from './app.routes'; import { provideClientHydration } from '@angular/platform-browser'; //This (provideHttpClient) will help us to resolve the issue  import {provideHttpClient} from '@angular/common/http'; export const appConfig: ApplicationConfig = {   providers: [ provideRouter(routes),  provideClientHydration(), provideHttpClient ()      ] }; The appConfig const is used in the main.ts file, see the code, main.ts : import { bootstrapApplication } from '@angular/platform-browser'; import { appConfig } from ...
Read more

React Lifecycle Components | Mounting, Updating, Unmounting

In React, each component has a life-cycle which manipulate during its three main phases. The following three phases are: 1.       Mounting 2.       Updating 3.       Unmounting React does so by “ Mounting ” (adding nodes to the DOM), “ Unmounting ” (removing them from the DOM), and “ Updating ” (making changes to nodes already in the DOM). Mounting - Lifecycle Phase 1 Mounting is used for adding nodes (elements) to the DOM. The React has four built-in methods that gets called, in this order, when mounting a component - 1.       constructor() 2.       getDerivedStateFromProps() 3.       render() 4.       componentDidMount() Note – 1)       The render() method is required and It always be called and the others methods are optional (you will call...
Read more

39 Best Yii2 Interview Questions and Answers - PHP Frameworks

1: What Is Yii framework? 2: Why Yii Is So Fast? 3: Yii Versions? 4: What Are the Prerequisites Yii? 5: Why Use Yii 2.0 Framework? 6: What Are the Benefits of Yii over other Frameworks? 7: What's New in Yii Release 2.0? 8: What Is The First File That Gets Loaded When You Run A Application Using Yii? 9: What Is The First Function That Gets Loaded From A Controller? 10: What Are the core components of Yii2 framework? 11: What Are the great feature of Yii Framework? 12: What Are The Application Structure of Yii 2.0 Framework? 13: What Are the Naming Convention in Yii 2.0 Framework? 14: What Is Request Life-Cycle of Yii 2.0 framework? 15: What Are Yii helpers? 16: What Are the Core Helper Classes in Yii Framework? 17: What Are The Server Requirements to Install Yii 2.0 Framework? 18: How To Customizing Helper Classes in Yii Framework? 19: What Are The Directory Structure of Yii 2.0 Framework? 20: How To Create Directory Structure of Yii Framewor...
Read more