Introduction:
SQL Server 2016 is a big step forward. Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and SP1 version (13.0.4001.0) with more new features summarized in this article :
I will introduce here 5 top new features:
- HEKATON Enhancements
- Stretch Database
- Polybase
- JSON Support
- Temporal Tables
HEKATON Enhancements:
HEKATON was introduced in SQL Server 2014. Although there were many good reasons to use Memory-Optimized tables in the first release of In-Memory OLTP, several limitations restricted the number of use cases for which In-memory OLTP was suitable. In this section, I will describe many enhancements that make putting memory-optimized tables to good use easier. All of the points below are supported by SQL Server 2016::
- FOREIGN KEY constraints between Memory-Optimized tables.
- UNIQUE AND CHECK constraints.
- UNIQUE indexes
- Triggers (AFTER) for INSERT/UPDATE/DELETE operations are now available to use with Memory-Optimized tables, as long as you use WITH NATIVE_COMPILATION.
- Columns with large object types like (varchar(max), nvarchar(max), and varbinary(max)) can be used now.
- Collation using any code page supported by SQL Server.
- A non-unique index can allow NULL values in its key.
- Now we can ALTER Memory-Optimized tables
Stretch Database:
One of the new features provides Hybrid-Cloud environments in SQL Server 2016 and it allows us to move our cold data with a very transparent way to Microsoft Azure cloud which means there will be no more changes to the existing queries or applications. We set the policy that determines where the data should be stored and SQL server handles the data movement in the background. The table is always online and can access easily through the application.
Stretch database benefits
- Less cost for cold data
- There are no changes to queries or applications
- Reduce On-premises maintenance time and storage usage for data
- Keeps your data secure even during migration
Hence now , to configure a database to stretch database run the enable database for stretch wizard by right click on the desired database, choose Tasks, then select stretch and then click enable to launch the following wizard:
Figure 1 enable database for stretch wizard
You will go through its sections providing the required information about the tables you want to enable for stretch ,information about your Windows Azure subscription, information about your database master key and the IP address of your SQL Server to create a firewall rule on Azure that lets SQL Server communicate with the remote Azure server.
Polybase:
PolyBase was introduced in SQL Server 2014 as an interface exclusively for Microsoft Analytics Platform System (APS; formerly known as Parallel Data Warehouse) with which you could access data stored in Hadoop Distributed File System (HDFS) by using SQL syntax in queries. In SQL 2016, you can use PolyBase to query data in Hadoop or Azure Blob Storage and combine the results with relational data stored in SQL Server.
Polybase benefits
To keep it simple, by using polybase you can:
- Query data stored in Hadoop using standard T-SQL statements.
- Query data stored in Azure blob storage using standard T-SQL statements.
- Import data from Hadoop, Azure blob storage, or Azure Data Lake Store without ETL or any other import tools.
- Export data to Hadoop, Azure Blob Storage or Azure Data Lake Store and keep it online.
- Integrate with BI tools.
You can install only one instance of PolyBase on a single server which must also has a SQL Server instance installed because the PolyBase installation process adds the following 3 databases: DWConfiguration, DWDiagnostics, and DWQueue.
The installation process also adds the PolyBase engine service and PolyBase data movement service to the server.
PolyBase Installation Requirements
- Installed software: Microsoft.NET Framework 4.5 and Oracle Java SE RunTime Environment (JRE) version 7.51 or higher (64-bit).
- Minimum memory: 4 GB.
- Minimum hard disk space: 2 GB.
- TCP/IP connectivity enabled
PolyBase Installation Wizard:
- Select PolyBase Query Service for External Data on the Feature Selection page as shown in figure2
Figure 2 Enabling polybase feature in the installation wizard
- Next, on the PolyBase Configuration page, you specify whether your SQL Server instance is a standalone PolyBase instance or part of a PolyBase scale-out group:
Figure 3 polybase configuration steps in the installation wizard
- Then on the Server Configuration page, you must configure the SQL Server PolyBase engine service and the SQL Server PolyBase data movement service to run under the same account.
JSON Support:
JSON is data in text format and it is popular because its language independence and simple data structure make it easy to use in modern web and mobile applications. With the addition of JSON support to SQL Server 2016, you can now support both relational and nonrelational data structures in your environment and choose the appropriate model for your data requirements and workloads.
Using new T-SQL functions and operators, you can easily export relational data to JSON or parse and import JSON into relational storage, even if you are working with in-memory OLTP, columnstore, or temporal relational tables. You can also write queries that combine results of relational and JSON data. Working with JSON in SQL Server is much like working with XML, except JSON is not a data type.
As a quick example to get familiar with the structure of JSON data, if we executed the following code:
use SQL_shack2016
SELECT TOP 3 [ID], [FIRSTNAME], [LASTNAME]
FROM [dbo]. [Students]
FOR JSON AUTO;
We will get this result:
[{“ID”:”526993″,”FIRSTNAME”:” Lazaro”,”LASTNAME”:”Oneal”}
, {“ID”:”710325″,”FIRSTNAME”:” Dorie”,”LASTNAME”:”Salinas”}
,{“ID”:”496813″,”FIRSTNAME”:” Mathilde”,”LASTNAME”:”Hooper”}]
Statements and functions are included in JSON support:
- Using FOR JSON clause with select statement for exporting data as JSON as mentioned in the previous example.
- Using OPENJSONfunction for importing JSON data into SQL Server, or converting JSON data to rows and columns.
- So to reverse JSON data we got in the previous example we will execute the following code:
To get the following result:
SELECT * FROM OPENJSON (‘{“ID”:”526993″,”FIRSTNAME”:” Lazaro”,”LASTNAME”:”Oneal”}
, {“ID”:”710325″,”FIRSTNAME”:” Dorie”,”LASTNAME”:”Salinas”}
, {“ID”:”496813″,”FIRSTNAME”:” Mathilde”,”LASTNAME”:”Hooper”}’)
- The ISJSONfunction is to test if a string contains valid JSON.
- The JSON_VALUEfunction extracts a scalar value from a JSON string.
- The JSON_QUERYfunction extracts an object or an array from a JSON string.
- The json_modifyfunction updates the value of property in a JSON string and returns the updated JSON string.
Temporal Tables:
Temporal tables introduced as a new standard in ANSI SQL 2011. Also Transact-SQL has been extended to support the creation of temporal tables and the querying of these tables about a specific point in time. A temporal table allows you to find the state of data at any point in time.
When you create a temporal table, the system creates 2 tables. One table is the current table (also known as the temporal table), and the other is the history table. The history table is created as a page-compressed table by default to reduce storage utilization. As data changes in the current table, the database engine stores a copy of the data as it was before the change in the history table.
Temporal table limitations and restrictions:
- System versioning and the FileTable and FILESTREAM features are incompatible.
- You cannot use CASCADE options when a temporal table is a referencing table in a foreign-key relationship.
- You cannot use INSTEAD OF triggers on the current or history table, although you can use AFTER triggers on the current table.
As a simple example to create a Temporal Table by the following code:
use [SQL_shack2016]
CREATE TABLE [Teachers_Temporal_Auto] (
[TeacherID] [int] NOT NULL,
[FistName] [nvarchar] (50) NOT NULL,
[LastName] [nvarchar] (50) NOT NULL,
[Address] [nvarchar] (100) NOT NULL,
[Department] [nvarchar] (100) NOT NULL,
[HireDate] [datetime] NOT NULL,
CONSTRAINT [PK_Teachers_Temporal_Auto_TeacherID] PRIMARY KEY CLUSTERED
(
[TeacherID] ASC
), ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);
We can now preview this created table in SSMS like the following figure:
Figure 4 Temporal Tables in SSMS
For more information in Temporal tables check this article Concept and basics of Temporal tables in SQL Server 2016