New Features in SQL Server 2016 - Part 2

Good Morning Guys,

As we have seen the top 3 new features in SQL Server 2016, here is the continuation of that post. We will see few other enhancements in it.

Following are the list extending to these top 3 features:

4. Enhancements to AlwaysOn:

As everyone knows the concept of AlwaysON was introduced by Microsoft in SQL Server 2012. This was introduced due to many limitations in the previous versions of SQL Server on High Availability and Disaster Recovery AlwaysON was introduced for mission critical Enterprise Application.

It was released for only Enterprise Edition but in SQL Server 2016 it will be released in Standard Edition of course with limited functionality. AlwaysON has also seen enhancement in SQL Server 2014 as well.

There is 1 Primary replica, 8 Secondary replicas and upto 3 replicas can be synchronized compared to 2 secondary replica can be synchronized in 2014.

Distributed transaction or MSDTC was not supported between Databases on AG in 2012 and 2014; which is now supported in SQL Server 2016.

We can now perform Full, File, File group or Log backups on the secondary replica Databases, but can't perform Differential Backup.

For more detail please refer to this blog on AlwaysON.

5. Native JSON Support:

JSON stands for JavaScript Object Notation. This is mainly provided for the developers, who will use this function for organizing the data in more logical manner. It is much more similar to XML already available by SQL Server.

It format the SQL result set as JSON by adding FOR JSON clause in the From statement. It is very much similar to XML in terms of types as well:

• RAW
• AUTO
• Explicit

Following is the syntax for using the JSON:

SELECT column, expression, column as alias
 FROM table1, table2, table3
 FOR JSON [AUTO | PATH]

Figure 1 shows the demo how the JSON script be written from a SQL Table:

Figure 1

Please refer the msdn blog here for more detail on JSON.


6. Enhancement In-Memory OLTP:

The feature of In-memory OLTP was introduced in SQL Server 2014 with the name "Hekaton" which is derived from a Greek which means 'one hundred' i.e. the goal of the project was to see 100x performance appraisal.

It has a separate query processor engine which is lock free design but at the same time it also maintain the data transnational integrity. Due to which it is different from Pin Table in SQL Server 6.5 release or putting Databases in SSD's (Solid State Disks). Refer this link to get an idea about the architecture of In-memory OLTP.

When it has done the debut it came with lots of drawback. And now from these demerits many has been converted to merit. lets see some of them below:

    i.  The max table size 256 GB which has changed to 2 TB.

    ii. TDE feature which is the greatest security feature in SQL Server was not supported in 2014 version, but now in 2016 it is supported.

    iii. Another major drawback clause such as IN, EXISTS, DISTINCT,OR,NOT,OUTER JOIN was not supported for query. Now these clauses are supported in SQL Server 2016.

    iv. LOB data type such as Varbinary(MAX) or Varchar(MAX) were not supported by the released version but from SQL Server 2016 it is supported.

    v. Similar way Foreign Key was not supported in older version which is not supported in SQL Server 2016.

There are many other new properties which has been enhanced in the new product of SQL Server 2016 for In-Memory OLTP check this msdn link for more detail.

These were the few new and some enhancement done in SQL Serve 2016. Check out this link which has some series of SQL unplugged videos for more details. So guys get ready to work with these exciting new features of SQL Server 2016 for Administrators.

Also along with features for DBA many more are waiting for BI and Developers guys as well.