Following are the some of the new features of the Sql Server 2016 which I have blogged. Click on the feature name to know it in detail with extensive list of examples:
1. DROP IF EXISTS Statement in Sql Server 2016
In Sql Server 2016, IF EXISTS is the new optional clause introduced in the existing DROP statement. Basically, it checks the existence of the object, if the object does exists it drops it and if it doesn’t exists it will continue executing the next statement in the batch. Basically it avoids writing if condition and within if condition writing a statement to check the existence of the object.
In Sql Server 2016 we can write a statement like below to drop a Stored Procedure if exists.
--Drop stored procedure if exists DROP PROCEDURE IF EXISTS dbo.WelcomeMessage
In Sql Server 2016 we can write a statement like below to drop a Table if exists.
--Drop table Customer if exists DROP TABLE IF EXISTS dbo.Customers
2. STRING_SPLIT function in Sql Server 2016
STRING_SPLIT is one of the new built-in table valued function introduced in Sql Server 2016. This table valued function splits the input string by the specified character separator and returns output as a table.
SYNTAX:
STRING_SPLIT (string, separator)
Where string is a character delimited string of type CHAR, VARCHAR, NVARCHAR and NCHAR.
Separator is a single character delimiter by which the input string need to be split. The separator character can be one of the type: CHAR(1), VARCHAR(1), NVARCHAR(1) and NCHAR(1).
Result of this function is a table with one column with column name as value.
EXAMPLE: This example shows how we can use STRING_SPLIT function to splits the comma separated string.
SELECT *
FROM STRING_SPLIT('Basavaraj,Kalpana,Shree',',')
RESULT:
To understand STRING_SPLIT function with extensive list of examples you may like to go through the article: STRING_SPLIT function in Sql Server 2016
3. GZIP COMPRESS and DECOMPRESS functions in Sql Server 2016
COMPRESS and DECOMPRESS are the new built in functions introduced in Sql Server 2016.
COMPRESS function compresses the input data using the GZIP algorithm and returns the binary data of type Varbinary(max).
DECOMPRESS function decompresses the compressed input binary data using the GZIP algorithm and returns the binary data of type Varbinary(max). We need to explicitly cast the output to the desired data type.
These functions are using the Standard GZIP algorithm, so a value compressed in the application layer can be decompressed in Sql Server and value compressed in Sql Server can be decompressed in the application layer.
Let us understand these functions with examples:
Example: Basic Compress and Decompress function examples
SELECT COMPRESS ('Basavaraj')
RESULT:
0x1F8B0800000000000400734A2C4E2C4B2C4ACC0200D462F86709000000
Let us decompress the above compressed value using the DECOMPRESS function by the following script
SELECT DECOMPRESS( 0x1F8B0800000000000400734A2C4E2C4B2C4ACC0200D462F86709000000)
RESULT:
0x42617361766172616A
From the above result we can see that the result of the DECOMPRESS function is not the actual value but instead it is a binary data. We need to explicitly cast the result of the DECOMPRESS function to the datatype of the string which is compressed.
Let us cast the result of the DECOMPRESS function to Varchar type by the following statement.
SELECT CAST(0x42617361766172616A AS VARCHAR(MAX))
RESULT:
Basavaraj
4. SESSION_CONTEXT in Sql Server 2016
In .Net we have Session object which provides a mechanism to store and retrieve values for a user as user navigates ASP.NET pages in a Web application for that session. With Sql Server 2016 we are getting the similar feature in Sql Server, where we can store multiple key and value pairs which are accessible throughout that session. The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the SESSION_CONTEXT built in function.
EXAMPLE: This example demonstrates how we can set the session context key named EmployeeId with it’s value and retrieving this set keys value.
--Set the session variable EmployeeId value EXEC sp_set_session_context 'EmployeeId', 5000 --Retrieve the session variable EmployeeId value SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId
RESULT:
5. Compare Execution Plans in Sql Server 2016
Comparing two execution plans is one of the new feature which is getting introduced in Sql Server 2016. This will be one of the good addition to the Sql Server features set. Many a time we come across a scenario where we need to compare the two execution plans. For example some time we want to see what is the difference in the execution plan after making some changes to it. Sometimes we come across a scenario where we observe that some stored procedure is perfectly working fine in development/System test environment but not in the production environment. In such scenario comparing the Dev/QA execution plan with production execution plan gives us the clue on what is going wrong. Without this feature currently we open the two execution plans in separate window and then we manually compare, it is tedious to figure out quickly what is going wrong.
With this new feature we can comapre two execution plans and their properties as shown in the below image
6. Live Query Statistics in Sql Server 2016
Live Query Statistics is one of the new feature introduced in Sql Server 2016, it basically provides the real-time live execution plan of an active running query to the Developer/DBA.
This SSMS feature is very helpful in checking the query execution progress for a long running queries, currently we don’t know for a long running queries where it is stuck or which specific operator is taking long time or how much percentage is completed or approximately how much extra duration required to complete it etc. This feature provides a way to know all these at any given point during the query execution.
It also helps in debugging the queries without needing to wait for the completion of the query execution. As soon as the query execution starts we can see the real-time live execution plan with moving dotted lines between the operators, operator execution progress, overall query execution progress etc.
To get the live query execution plan, just like Actual Execution plan option we need to select the Include Live Query Statistics option as shown in the below image before executing the query
Below GIF animation shows an example of live execution plan where we can see moving dotted lines between the operators, operator execution progress, overall query execution progress etc
*Double click on the image to get the enlarged view
7. Native JSON Support in Sql Server 2016
Native JSON (Java Script Object Notation) support is one of the new feature that is coming with Sql Server 2016.
JSON implementation in Sql server is on the similar lines as that of XML. One major difference is: JSON doesn’t have native JSON data type like the XML data type. We can store JSON data in regular NVARCHAR/VARCHAR column.
Below are the main JSON features introduced in Sql Server 2016. Click on the link to understand each of these features with an extensive list of examples.
- FOR JSON Clause: can be used to convert the query result (rows) to JSON format.
- OPENJSON Function : is very much similar to the OPENXML function, it provides a rowset view of the JSON. It is basically a table valued function, that can be used to transform the JSON value to one or many rows
- ISJSON Function: This function validates whether the parameter supplied to it is a well formed JSON or not
- JSON_VALUE Function: This function returns the scalar value from the input JSON text from the specified JSON path location
- JSON_QUERY Function: This function extracts the JSON fragment (i.e. JSON object or an array) from the input JSON string from the specified JSON path.
- Indexing Strategy for JSON Value in Sql Server 2016
- lax and strict JSON Path modes in Sql Server 2016
8. Temporal Tables in Sql Server 2016
Temporal Table is a new type of user defined table introduced in Sql Server 2016. Temporal table is like any other normal tables the main difference is for Temporal Tables Sql Server automatically tracks the full history of the data changes into a separate history table. Because of the tracking of the fully history of the data, it provides a mechanism to know the state of the data at any point in time.
With regular tables we can only know the current/latest state of the data, we will not be able to see the past state of the updated or deleted records. For regular tables if we need to keep track of the history developer need to create triggers and store the data in separate table there is no built in support for it. But with Temporal Tables Sql Server provides the built-in support for tracking the full history of the data and also provides the temporal querying construct FOR SYSTEM_TIME to query historic data at any point in time or for a period of time. Because of this built in support by the database engine to track the history of the data, Temporal Tables are referred as system-versioned temporal tables/system versioned tables.
- Below are the some of the common uses of the System-Versioned Temporal Tables
- It provides a mechanism to Audit the data changes as the complete history is maintained
- Recovering from accidental data changes. For instance if someone has wrongly deleted a record, because of the availability of the history data we can easily recover these deleted records.
- Maintain a Slowly Changing Dimension (SCD) for decision support applications
- Part 1: Introduction to Temporal Tables and DML operations on the Temporal Table
- Part 2: Querying System-Versioned Temporal Table
- Part 3: Enabling System Versioning for an Existing Regular Table
- Part 4: DDL operations on the System-Versioned Temporal Table
9. Row level security in Sql Server 2016
Row level security is one of the new feature introduced in Sql Server 2016, it provides a mechanism to control row level read and write access based on the user’s context data like identity, role/group membership, session/connection specific information (I.e. like CONTEXT_INFO(), SESSION_CONTEXT etc) etc.
The logic to control the access to table rows resides in the database and it is transparent to the application or user who is executing the query. For example a database user executing a query SELECT * FROM Customers may feel that he has complete access to the Customers table as this query is returning the result without any exception, but with row level security in-place we can make the DataBase engine internally change the query something like for example: SELECT * FROM Customers Where AccountManager = USER_NAME().
Parts of Row-Level Security
Following are the three main parts of a Row-Level Security
- Predicate Function
A predicate function is an inline table valued schema bound function which determines whether a user executing the query has access to the row based on the logic defined in it. - Security Predicate
Security Predicate is the one which binds a Predicate Function to the Table.There are two types of security predicates
- Filter Predicate
It filters-out the rows from the SELECT, UPDATE or DELETE operation to which user doesn’t have access based on the logic in the Predicate function. This filtering is done silently without notifying or raising any error. - Block Predicate
It blocks user from INSERT, UPDATE or DELETE operation by explicitly raising the error if the row doesn’t satisfy the predicate function logic.There are four types of BLOCK predicates AFTER INSERT, BEFORE UPDATE, AFTER UPDATE and BEFOR DELETE.
- Filter Predicate
- Security Policy
Security policy is a collection of a Security Predicates which are grouped in a single new object called Security Policy.
10. Dynamic Data Masking in Sql Server 2016
Dynamic data masking is one of the new Security Feature introduced in Sql Server 2016. It provides a mechanism to obfuscate or mask the data from non-privileged users. And the users with sufficient permission will have complete access to the actual or un-masked data.
Traditionally, if we see the application layer takes care of masking the data and displaying it. For example: from database layer we will get a clear SSN number like 123-321-4567, but the application will mask and display it to the user as XXX-XXX-4567. With dynamic data masking from database layer only we can return the query result with masked data if user doesn’t have sufficient permission to view the actual/Unmasked data.
Dynamic data masking functions/rule can be defined on the table columns for which we need the masked out-put in the query result. It doesn’t change the actual value stored in the column. Masking function is applied on the query result just before returning the data, if user doesn’t have the enough permission to get the un-masked data. But user with db-owner or UNMASK permission will get the un-masked data in the query result for the masked columns. Masked out-put will be of the same data type as the column data type, in that way we can readily use this feature without really needing changes to the application layer.
Following are the four masking functions which can be defined on table column
- Default
- Partial
- Random
To understand Dynamic Data Masking and each of the above four masking functions with extensive list of examples you may like to go through the article: Dynamic Data Masking in Sql Server 2016
11. Query Store in Sql Server 2016
Many a time we come across a scenario where suddenly in production without any release or changes some query which was working perfectly alright till yesterday is taking too long to execute or consuming lot of resources or timing out.
Most of the times such issue are related to execution plan change (commonly referred as Plan Regression). Till yesterday the query was running fine as it was running with good cached execution plan and today a bad plan is generated and cached. Because of this bad cached plan the query which was executing perfectly alright suddenly starts misbehaving.
To identify and fix such performance problems due to the execution plan change the Query Store feature introduced in Sql Server 2016 will be very handy.
Query Store basically captures and stores the history of query execution plans and its performance data. And provides the facility to force the old execution plan if the new execution plan generated was not performing well.
To understand how the Query Store feature comes handy in resolving performance issues with extensive list of examples you may like to read the article Query Store in Sql Server 2016
For a Query Store feature enabled database we can see the Query Store folder in the SSMS with various options like Regressed Queries, Top Resource Consuming Queries etc as shown in the below image:
As shown in the below image Query Store gives an option to view and compare various execution plans for a query and force the execution plan for the future execution of the query.
12. Page allocation changes in Sql Server 2016
In the versions of Sql Server prior to Sql Server 2016 by default first 8 pages for the tables were from a mixed extent at one page at a time and subsequent pages were from an uniform extent. Microsoft in the versions of Sql Server prior to 2016 provided an option to override this behavior by means of Trace Flag 1118. If this trace flag is enabled, the first 8 data pages for the tables were also from Uniform Extent instead of Mixed Extent. This flag was helpful to avoid the resource contention issues, especially in the TempDB in the scenario’s where huge number of temp tables were created.
Below is the page allocation mechanism in the TempDb and User Databases of Sql Server 2016
- TempDB Database
With Sql Server 2016, TempDb database objects by default will get the pages from the Uniform Extent.And there is no option to over-ride this behaviour. There is no effect of the Trace Flag 1118 on the page allocation behavior. - User Databases
With Sql Server 2016, even the user databases objects by default will get the pages from the Uniform Extent. We can change this behavior by setting the database property MIXED_PAGE_ALLOCATION by using the ALTER DATABASE statement.
Below image explains how the page allocation happens in the version of Sql Server prior to Sql Server 2016 and in Sql Server 2016. In the example demonstrated below each record which is inserted is of 8000 bytes. From the below image we can see that in the versions of Sql Server prior to 2016 insertion of the first record resulted in reserving 16KB space (i.e. 1 8KB data page from mixed extent + 1 8KB Index Allocation MAP(IAM) Page from the mixed extent). For each subsequent record insertion till the 8th record one 8KB data page is allocated from the mixed extent. In Sql Server 2016 we can see that insertion of the first record resulted in reserving 72KB space (i.e. 1 Uniform 64 KB extent + 1 8KB Index Allocation MAP(IAM) Page from the mixed extent). And the subsequent 7 records insertion is using the remaining 7 pages from the uniform extent allocated during the first record insertion.
To understand Page allocation changes in Sql Server 2016 with extensive list of examples you may like to go through the article: Page allocation changes in Sql Server 2016
13. DATEDIFF_BIG Function in Sql Server 2016
DATEDIFF_BIG function like DATEDIFF function returns the difference between two dates. The difference between these two functions is the return type. DATEDIFF functions return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.
Example: Below example demonstrates how DATEDIFF and DATEDIF_BIG functions behave differently when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.
SELECT DATEDIFF(ms, '2015-12-01', '2015-12-31') 'DateDiff'
RESULT:
Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
SELECT DATEDIFF_BIG(ms, '2015-12-01', '2015-12-31') 'DateDiff'
RESULT:
So, if we know that sometime the difference between two dates is going to cross the INT max value then we have to use the DATEDIFF_BIG function
SUMMARY
Following are the some of the new features of the Sql Server 2016 which I have blogged. Click on the feature name to know it in detail with extensive examples: