Tag Archives: Sql Server 2016

Page allocation changes in Sql Server 2016

This article explains the changes in the page allocation mechanism in Sql Server 2016 with extensive list of examples

Comparision of Page allocation in TempDB

Introduction to Page and Extent in Sql Server

In Sql Server database object (i.e. Table/Index) data is stored on an 8KB data pages and a set of 8KB contiguous data pages form an extent of 64KB. In Sql Server memory management happens at extent level. There are two types of extents in Sql Server:

  • Uniform Extent: An extent whose all 8 pages are reserved or filled with one Table data is referred as uniform extent
  • Mixed Extent: An extent whose 8 pages are shared by multiple objects is referred as a mixed extent. At max a mixed extent can have pages belonging to 8 different tables

[ALSO READ] Live Query Statistics in Sql Server 2016

Page allocation mechanism in the versions of Sql Server prior to Sql Server 2016

Prior to Sql Server 2016 by default first 8 pages for the tables were from a mixed extent 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.

[ALSO READ] STRING_SPLIT function in Sql Server 2016

Page allocation mechanism in Sql Server 2016

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.

[ALSO READ] GZIP COMPRESS and DECOMPRESS functions in Sql Server 2016

Sys.Databases catalog view changes

In Sql Server 2016, the Sys.Databases catalog view has an additional column is_mixed_page_allocation_on. This column value 1 means the database table will get the first 8 pages from the mixed extent and subsequent pages from the uniform extent. And a value 0 means all the pages for the table are from the uniform extent.

Execute the following query to check the page allocation mechanism for the user and system databases

SELECT name, is_mixed_page_allocation_on 
FROM Sys.Databases

RESULT:
Default Page Allocation Setting for TempDB and User Database

From the result we can see that for the TempDB and user databases the mixed page allocation is OFF. It means TempDB and user databases in Sql Server 2016 by default will have the page allocated from uniform extent.

[ALSO READ] Dynamic Data Masking in Sql Server 2016

EXAMPLES

Let us understand these page allocation changes with below examples

EXAMPLE 1: This example compares the page allocation mechanism in user database between Sql Server 2016 and the previous versions of the Sql Server.

Page allocation mechanism in Sql Server 2016 user databases

Create a demo database SqlhintsPageAllocation with a DemoTable table by executing the following script in Sql Server 2016:

--Create a Demo database
CREATE DATABASE SqlhintsPageAllocation 
GO
USE SqlhintsPageAllocation
GO
--Create a demo Table
CREATE TABLE DemoTable
(
	DemoColumn CHAR(8000)
)
GO
--Check the space usage of the DemoTable
SP_SPACEUSED DemoTable

RESULT:
Initial Space Usage

In one 8KB data page for data 8060 bytes is reserved. In the DemoTable, the DemoColumn is of type CHAR(8000) it means even when we store just one character in this column it will take 8000 bytes which is almost equivalent to 1 data page. So, we can consider 1 row in the DemoTable require 1 data page. From the above result we can see that when table is created initially no space is reserved for it.

Execute the following statement to insert 9 rows in to the DemoTable and display the space used after inserting each record:

INSERT INTO DemoTable VALUES ('A')
EXECUTE SP_SPACEUSED DemoTable
GO 9 

RESULT:
User Database Uniform Extent Allocation
[ALSO READ] GO Statement can also be used to excute batch of T-Sql statement multiple times

From the result 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). Each table will have at the least one IAM page, IAM page tracks all the pages and extents associated with a table. A table will have separate IAM page and it’s IAM page chain for the In-Row data, row overflow data and lob data. IAM pages will always be from the mixed extents.

Insertion of the 9th record resulted in the allocation of one more uniform extent, because that after the 9th record insertion we can see that reserved space is 136 KB( 72 KB + one more 64 KB uniform extent).

Execute the following un-document statement to get the all the pages associated with the DemoTable. Please don’t experiment any of such un-documented statements in the production environment.

 
DBCC IND ('SqlhintsPageAllocation','DemoTable',-1)

RESULT:
Pages of the DemoTable

From the result we can see that DemoTable has 10 pages (1 IAM Page from mixed extent + 9 Data pages from the Uniform extents).

Page allocation mechanism in the older versions of Sql Server user databases

Execute the following statement to Create a demo database SqlhintsPageAllocation with a DemoTable table by executing the following script in the versions of the Sql Server older than Sql Server 2016. I am executing it on Sql Server 2012 instance.

--Create a Demo database in the version of Sql Sever
--which is older than Sql Server 2016
CREATE DATABASE SqlhintsPageAllocation 
GO
USE SqlhintsPageAllocation
GO
--Create a demo Table
CREATE TABLE DemoTable
(
	DemoColumn CHAR(8000)
)
GO
--Check the space usage of the DemoTable
SP_SPACEUSED DemoTable

RESULT:
Initial Space Usage in Older Sql Versions

Execute the following statement to insert 9 rows in to the DemoTable and display the space used after inserting each record:

User Database Mixed Extent Allocation Sql Server 2012

From the result we can see that 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 record insertion till the 8th record one 8KB data page is allocated from the mixed extent.

Insertion of the 9th record resulted in the allocation of one more uniform extent, after the 9th record insertion we can see that reserved space is 136 KB( 72 KB (previously alloated space) + one more 64 KB uniform extent).

Execute the following un-document statement to get the all the pages associated with the DemoTable. Please don’t experiment any of such un-documented statements in the production environment.

DBCC IND ('SqlhintsPageAllocation','DemoTable',-1)

Pages of the DemoTable in older versions

From the result we can see that DemoTable has 10 pages, one 8KB IAM Page from mixed extent and 9 Data pages (8 Pages from mixed extent and 1 page from uniform extent).

Summary: For user databases in the versions of Sql Server older than 2016, the first 8 data pages were allocated from the mixed extent and next pages from uniform extent. In Sql Server 2016 even the first eight data pages were allocated from the uniform extent. Below image summaries this page allocation mechanism comparison between Sql Server 2016 and older versions of Sql Server

Comparision of Page allocation in User Database
[ALSO READ] Row level security in Sql Server 2016

EXAMPLE 2: This example compares the page allocation mechanism in the TempDB database between Sql Server 2016 and the previous versions of the Sql Server.

Page allocation mechanism in Sql Server 2016 TempDB database

Create a temporary table #DemoTable table by executing the following script in Sql Server 2016:

USE TEMPDB
GO
--Create a demo Temp Table
CREATE TABLE #DemoTable
(
	DemoColumn CHAR(8000)
)
GO
--Check the space usage of the temp table: #DemoTable
SP_SPACEUSED #DemoTable
GO

RESULT:
TempDB12016

In one 8KB data page for data 8060 bytes is reserved. In the #DemoTable, the DemoColumn is of type CHAR(8000) it means even when we store just one character in this column it will take 8000 bytes which is almost equivalent to 1 data page. So, we can consider 1 row in the #DemoTable require 1 data page. From the above result we can see that when table is created initially no space is reserved for it.

Execute the following statement to insert 8 rows in to the #DemoTable temp table and display the space used after inserting each record:

INSERT INTO #DemoTable VALUES ('A')
EXECUTE SP_SPACEUSED #DemoTable
GO 8 -- loop 8 times

RESULT:
Uniform Extent Allocation for TempDB

From the result 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). Each table will have at the least one IAM page, IAM page tracks all the pages and extents associated with a table. A table will have separate IAM page and it’s IAM page chain for the In-Row data, row overflow data and lob data. IAM pages will always be from the mixed extents.

Page allocation mechanism in the older versions of Sql Server TempDB

Create a temporary table #DemoTable table by executing the following script in the versions of the Sql Server older than Sql Server 2016. I am executing it on Sql Server 2012 instance.

USE TEMPDB
GO
--Create a demo Temp Table
CREATE TABLE #DemoTable
(
	DemoColumn CHAR(8000)
)
GO
--Check the space usage of the temp table: #DemoTable
SP_SPACEUSED #DemoTable
GO

RESULT:
TempDB12012

Execute the following statement to insert 8 rows in to the temp table #DemoTable and display the space used after inserting each record:

INSERT INTO #DemoTable VALUES ('A')
EXECUTE SP_SPACEUSED #DemoTable
GO 8 -- loop 8 times

RESULT:
Mixed Extent Allocation for TempDB

From the result we can see that 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 record insertion till the 8th record one 8KB data page is allocated from the mixed extent.

Summary: In the versions of Sql Server older than 2016 for TempDB, by default the first 8 data pages were allocated from the mixed extent and next pages were from uniform extent. In Sql Server 2016 even the first eight data pages were allocated from the uniform extent. Below image summaries this page allocation mechanism comparison between Sql Server 2016 and older versions of Sql Server

Comparision of Page allocation in TempDB
[ALSO READ] DROP IF EXISTS Statement in Sql Server 2016

Changing Page allocation mechanism at database level

By default in Sql Server 2016 both TempDB and User Database objects will get all the pages are allocated from the uniform extent. Sql Server 2016 provides a mechanism where we can change this behavior in the user database by the ALTER DATABASE statement. We an execute a script like the below one which is changing the SqlhintsPageAllocation database default seeting to alloate first 8 pages for the data base objects from uniform extent to mixed extent

SELECT name, is_mixed_page_allocation_on 
FROM Sys.Databases
WHERE name = 'SqlhintsPageAllocation'
GO
--Change SqlhintsPageAllocation page alloation setting
--to allocate first 8 pages from mixed extent
ALTER DATABASE SqlhintsPageAllocation 
SET MIXED_PAGE_ALLOCATION  ON
GO
SELECT name, is_mixed_page_allocation_on 
FROM Sys.Databases
WHERE name = 'SqlhintsPageAllocation'
GO

RESULT:
Changing MIXED_PAGE_ALLOCATION Setting

Let us execute the following statement to change TempDB database page allocation mechanism

ALTER DATABASE TempDB
SET MIXED_PAGE_ALLOCATION  ON

RESULT:

Msg 5058, Level 16, State 9, Line 1
Option ‘MIXED_PAGE_ALLOCATION’ cannot be set in database ‘tempdb’.

From the result we can see that TempDB database default page allocation mechanism can’t be changed. Where as user databases page allocation mechanism can be changed

[ALSO READ]:

New features in SQL SERVER 2016

Query Store in Sql Server 2016

Introduction to Query Store

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 Query Store with an example let us create a demo database SqlhintsQSDemo and a Customer table having sample data as shown in the following image by executing the following statement:

Customer Table for Query Store Demo

Script:

--------Create Demo database--------
CREATE DATABASE SqlhintsQSDemo
GO
USE SqlhintsQSDemo
GO
--------Create a Customer Table------
CREATE TABLE dbo.Customer( 
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Populate 100,000 customers with unique FirstName 
INSERT INTO dbo.Customer (FirstName, LastName)
SELECT TOP 100000 NEWID(), NEWID()
FROM SYS.all_columns SC1 
	CROSS JOIN SYS.all_columns SC2
GO 
--Populate 15000 customers with FirstName as Basavaraj
INSERT INTO dbo.Customer (FirstName, LastName)
SELECT TOP 15000 'Basavaraj', NEWID()
FROM SYS.all_columns SC1
        CROSS JOIN SYS.all_columns SC2

Execute the following statement to create a non-clustered index on the FirstName column of the customer table and the stored procedure GetCustomersByFirstName to get the customer details by FirstName

-- Create non-clustered index on the FirstName column
CREATE INDEX IX_Customer_FirstName on dbo.Customer (FirstName)
GO
-- Create stored procedure to get customer details by FirstName
CREATE PROCEDURE dbo.GetCustomersByFirstName
(@FirstName AS NVARCHAR(50))
AS
BEGIN
	SELECT * FROM dbo.Customer 
	WHERE FirstName = @FirstName
END

Enabling Query Store

Query store is a database level feature, which is disabled by default. Follow the following steps to enable it by using Management Studio

Step 1: Right click on the demo database SqlHintsQSDemo and select the Properties menu option as shown in the below image

Enable Query Store DB Properties window

Step 2: Step 1 Pops-up the properties window. In the properties window from the left navigation options select the new option Query Store. Below is the view of the properties window after selecting the Query Store option.

Query Store Properties window

Step 3: From the Query Store option window of Step 2 we can see that the Operation Mode (Requested) setting has three different options. Selecting the ReadWrite option will enable the Query Store and starts capturing the query execution data such as execution plan and it’s performance stats. Selecting Read option will only allow the data to read from the query store but no new query data is captured.
Below image shows the various query store options after selecting the ReadWrite Operation Mode. To know each of these options select the option it will display the details about it below.

Query Store Properties window Post Selecting ReadWrite

Query store captured data is stored in the respective query store enabled database. As we can see from the above image 100 MB is reserved for query store data in the SqlhintsQSDemo database. This value can be changed by changing the Max Size (MB) option value.

Click OK after selecting the desired query store options, it will enable the query store. And if we refresh the SqlhintsQSDemo database in the object explorer we can see the Query Store folder as shown in the below image

Query Store folder in the object explorer

Now query store feature is enabled let us understand how it comes handy in resolving performance issues

Example Usage of Query Store feature to resolve Performance issue

This example explains the scenario where suddenly in production without any release or changes some query which was working perfectly alright, starts taking too long to execute or consuming lot of resources or timing out. And how the query store comes handy in such situation

To explain this we will use the stored procedure GetCustomersByFirstName to fetch the records from the Customer table based on FirstName. And the Customer table has unique FirstName for all the customers except for the FirstName ‘Basavaraj’. There are 15,000 customers with FirstName as ‘Basavaraj’, so fetching a record where CustomerName is ‘Basavaraj’ will be better with table scan (i.e. clustered index scan) than using the non-clustered index on FirstName. But fetching the Customer records where FirstName value is anything other than ‘Basavaraj’, it makes sense to use the non-clustered index on FirstName. In this case where the majority queries will be better of executing by using the Non-clustered index on FirstName

Let us execute the following statement to get the details of the Customer whose FirstName is Xyz. While executing the below statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:
Query doing Non Clustered Index Seek

From the execution plan we can see that the Stored Procedure is using the Non-clustered index seek on the FirstName column to fetch the Customer details, which is the optimal option too.

From the object explorer select the “Top Resource Consuming Queries” option as shown in the below image to view the query plan and the performance stats capture for the above SP execution in the Query Store

Top Resource Consuming Queries Option

Below is the Query Store view of the “Top Resource Consuming Queries”. Here we can see that Plan Id 6 is corresponding to the above stored procedure execution which was using Non-Clustered Index Seek. Mouse over on the bar corresponding to the query which got executed by the SP GetCustomersByFirstName shows that it is executed ones, has one execution plan, it took 16129 micro seconds and query id is 6.

Qury Store After First Execution of the Query

Now the execution of the GetCustomersByFirstName will always use the Non-Clustered index on the FirstName column, as the first time execution of it cached this Non-Clustered Index Seek plan. So as long as this plan is cached the query where we need to get the Customer data by FirstName will always execute optimally. The only way this cached query plan is removed from the cache if the server is re-started or the memory pressure on the server etc. We can also execute the following DBCC statement to remove all the cached plans from the database server

DBCC FREEPROCCACHE

After executing the above DBCC statement all the cached plans are removed from the cache. Now try executing the same stored procedure i.e. GetCustomersByFirstName but this time pass the @FirstName parameter value as Basavaraj instead of Xyz. While executing the following statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Basavaraj'

Result:
Query doing Clustered Index San

From the result we can see that instead of using Non-Clustered Index on FirstName, it is doing the Clustered Index Scan. The reason it is doing clustered index scan is, there are 15,000 records whose FirstName is ‘Basavaraj’ and there are no previously cached plans. It makes sense for the Sql Server to use Clustered Index scan, instead of using a Non-Clustered index seek which uses bookmark look-up for each of the record. Now this plan is cached and each time the SP: GetCustomersByFirstName is executed it will use the clustered index scan

Below is the view of the Query Store after executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Basavaraj’. You need to refresh the query store if it is already open. Here we can see a new plan with plan id 11 is generated which corresponds to the clustered index scan. So we can see that now we have two query plans corresponding to the query executed by the stored procedure GetCustomersByFirstName.

Query Store View with two plans for the Same query 1

Let us again execute the following statement to get the details of the Customer whose FirstName is Xyz. While executing the below statement select the “Include Actual Execution Plan” option.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:
Using worst Cached Plan

From the result we can see that this time the stored procedure execution is using the Clustered index scan instead of the non-clustered index scan which it was doing previously. To get the customer details whose FirstName is ‘Xyz’, from the result we can see it is using the cached plan which is optimized/compiled for the @FirstName parameter value ‘Basavaraj’. This is how a perfectly working query starts misbehaving without any release or changes.

Below is the view of the Query Store after executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’. You need to refresh the query store if it is already open. Mouse over on the bar corresponding to the query which got executed by the SP GetCustomersByFirstName shows that it is executed 3 times and has two execution plans i.e. 6 and 11 and plan 11 which is a clustered index scan is the current plan. In the below image we can clearly see than plan 11 is taking more time to execute compared to plan 6. So, in this case it is optimal to execute the query with plan 6 compared to plan 11.

Query Store View after  third execution

From the above query store view it is clear that plan 6 is the optimal plan for the execution of the stored procedure GetCustomersByFirstName compared to plan 11 as majority of the time the stored procedure is executed with a parameter value other than ‘Basavaraj’.

As shown in the below image query store gives an option to force the execution plan for the future execution of the queery. To force the plan select the plan 6 (i.e. light blue color circle) and then click on the Force Plan option.

Query Store force the execution plan 1

After forcing the plan 6 as shown in the above image. Now execute the GetCustomersByFirstName one more time with @FirstName parameter value as ‘Xyz’.

EXEC dbo.GetCustomersByFirstName @FirstName = N'Xyz'

Result:
Execution plan after forcing it by the query store

From the above result we can see that execution of the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’ is doing a non-clustered index scan compared to clustered index which it was doing previously.

Below is the view of the Query Store after forcing the execution plan 6 and executing the SP: GetCustomersByFirstName with @FirstName parameter value as ‘Xyz’. We can see that plan 6 circle has a right tick mark, which means this is the force plan which now will not change even after server re-start. As shown in the below image adjacent to Force Plan option we have an Un-force Plan which an be used to un-force this forced plan.

Query store view post forcing the plan

New features in SQL SERVER 2016

JSON_MODIFY Function in Sql Server 2016

JSON_MODIFY is one of the new JSON function introduced in Sql Server 2016. This function can be used to update the value of the property in a JSON string and returns the updated JSON string.

SYNTAX:

JSON_MODIFY (json_string, json_path , new_value)

WHERE
json_string : Is the JSON String which needs to be updated
json_path : Is the location of the property in the json_string, whose value needs to be updated
new_value : The new value for the property in the json_string

Let us understand this function with extensive list of examples.

EXAMPLE 1: Updating JSON Property Value

In this example the JSON_MODIFY function is used to update the FirstName property value from Basavaraj to Basav

SELECT
 JSON_MODIFY('{"FirstName":"Basavaraj","Last Name":"Biradar"}',
                   '$.FirstName','Basav') AS 'Updated JSON'

RESULT:
JSON_MODIFY Example 1

[ALSO READ] ISJSON Function in Sql Server 2016

EXAMPLE 2: Updating JSON property value where JSON_MODIFY function json_string and new_value are variables

This example is same as Example 1, the only difference here is instead of passing JSON string as a constant assigning it to the variable @json_string and passing this variable to the function. And the new value is set to the variable @new_value and passed it to the function.

DECLARE @json_string VARCHAR(100) 
	= '{"FirstName":"Basavaraj","Last Name":"Biradar"}',
	@new_value VARCHAR(50) = 'Basav'
SELECT JSON_MODIFY(@json_string,'$.FirstName',
                     @new_value) AS 'Updated JSON'

RESULT:
JSON_MODIFY Example 2

In the above example we can see that only the json_path is a string literal. Let us assign this also to a variable and pass it to the JSON_MODIFY function.

DECLARE @json_string VARCHAR(100) 
		= '{"FirstName":"Basavaraj","Last Name":"Biradar"}',
	@json_path VARCHAR(50) = '$.FirstName',
@new_value VARCHAR(50) = 'Basav'
SELECT JSON_MODIFY(@json_string, @json_path, 
                     @new_value) AS 'Updated JSON'

RESULT:

Msg 13610, Level 16, State 2, Line 5
The argument 2 of the “JSON_MODIFY” must be a string literal.

From the result we can see that the JSON PATH expression should always be a string literal

[ALSO READ] JSON_VALUE Function in Sql Server 2016

EXAMPLE 3: Adding Id property and it’s value to the JSON string

The below script adds the Id property to the JSON string

SELECT JSON_MODIFY(
   '{"FirstName":"Basavaraj","Last Name":"Biradar"}'
   , '$.Id', 1) AS 'Updated JSON'

RESULT:
JSON_MODIFY Insert Property Example 3

[ALSO READ] lax and strict JSON Path modes in Sql Server 2016

EXAMPLE 4: Impact of JSON Path mode on adding a new property to the existing JSON string

Execute the following statement which is same as the example 3 script, the only difference is in the json_path the JSON path mode strict is specified.

SELECT JSON_MODIFY(
      '{"FirstName":"Basavaraj","Last Name":"Biradar"}'
      , '$.Id', 1) AS 'Updated JSON'

RESULT:

Msg 13608, Level 16, State 2, Line 1
Property cannot be found on the specified JSON path.

From the result we can see that the strict path mode will not allow adding of a new property to the JSON string

Execute the following statement which is same as the above example script, the only difference is in the JSON path mode. Here in this statement the JSON path mode is lax and it is the default path mode when it is not specified.

SELECT JSON_MODIFY(
        '{"FirstName":"Basavaraj","Last Name":"Biradar"}'
		,'lax$.Id', 1 )	 AS 'Updated JSON'

RESULT:
JSON_MODIFY Insert Property lax path mode Example 4

From the result we can see that lax JSON Path mode allows adding a new JSON property. The JSON Path mode lax is the default path mode and if path mode is not specified it will consider it as lax. We can see that in example 3 when path mode is not specified we are able to add the new property.

[ALSO READ] JSON_QUERY Function in Sql Server 2016

EXAMPLE 5: This example shows how setting a NULL value for the JSON property will remove that property from the JSON string

SELECT JSON_MODIFY(
        '{"Id":1,"FirstName":"Basavaraj","Last Name":"Biradar"}'
		,'$.Id', NULL)	 AS 'Updated JSON'

RESULT:
Removing an Existing Property Example 5

[ALSO READ] FOR JSON Clause in Sql Server 2016

Example 6: This example explains how we can set a NULL value for a JSON property

In the example 5 we saw that setting a NULL value for a JSON property resulted in that property getting deleted from the JSON string. But if we have requirement where we need to set the NULL value for a JSON property value then in the JSON path we have to specify strict JSON path mode as in the below script:

SELECT JSON_MODIFY(
        '{"Id":1,"FirstName":"Basavaraj","Last Name":"Biradar"}'
		,'strict$.Id', NULL)	 AS 'Updated JSON'

RESULT:
Setting NULL value for a JSON property Example 6

[ALSO READ] OPENJSON Function in Sql Server 2016

EXAMPLE 7: This example shows how we can append a value to the JSON Array.

Execute the below statement to add the hobby Tennis to the Hobbies array:

SELECT JSON_MODIFY(
            '{"Id":1,"Name":"Basavaraj",
            "Hobbies":["Blogging","Cricket"]}',
            'append $.Hobbies','Tennis') AS 'Updated JSON'

RESULT:
Adding value to the JSON Array Example 7

[ALSO READ] STRING_SPLIT function in Sql Server 2016

EXAMPLE 8: This example shows how we can add a JSON object to the JSON string.

Execute the following statement to add Address which is of type JSON object to the JSON string:

SELECT JSON_MODIFY(
	'{"Id":1,"Name":"Basavaraj"}'
	,'$.Address'
	,'{"State":"KA","Country":"India"}') AS 'Updated JSON'

RESULT:
Adding JSON Object to JSON string Example 8

From the result we can see that JSON_MODIFY function is escaping the double quote in the JSON object which is getting added. The reason for this is JSON_MODIFY function is treating the JSON object as a normal text instead of valid JSON.

But if you want to avoid the escaping the JSON object which is getting added, use the JSON_QUERY function as shown below:

SELECT JSON_MODIFY(
    '{"Id":1,"Name":"Basavaraj"}'
    ,'$.Address'
    ,JSON_QUERY('{"State":"KA","Country":"India"}')) 
           AS 'Updated JSON'

RESULT:
Adding JSON Object to JSON string Example 8 1

[ALSO READ] DATEDIFF_BIG Function in Sql Server 2016

EXAMPLE 9: This example shows how we can modify the property value of JSON string stored in the Table Column.

Create a Customer table as shown in the below image with sample data by the following script

Customer Table
Script:

CREATE DATABASE SqlHintsJSONModify
GO
USE SqlHintsJSONModify
GO
CREATE TABLE dbo.Customer( 
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50), Detail NVARCHAR(MAX))
GO
INSERT INTO dbo.Customer ( Name, Detail )
VALUES 
  ('Basavaraj','{"Address":{"State":"KA","Country":"India"}}'),
  ('Kalpana','{"Address":{"State":"MH","Country":"India"}}')

Execute the following statement and observe that the customer Kalpana’s state property value in the Detail column is MH.

SELECT * FROM dbo.Customer WHERE Name = 'Kalpana'

RESULT:
DataBeforeUpdate

Execute the following statement to update the Customer Kalpana’s State to KA from MH.

UPDATE Customer
SET Detail = JSON_MODIFY(Detail , '$.Address.State','KA')
WHERE Name = 'Kalpana'

Now execute the following statement to check whether the customer Kalpana’s state value is updated to KA from MH.

SELECT * FROM dbo.Customer WHERE Name = 'Kalpana'

RESULT:
DataAfterUpdate

[ALSO READ]

New features in SQL SERVER 2016

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.

[ALSO READ] Row level security in Sql Server 2016

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.

[ALSO READ] New Features in Sql Server 2016

Following are the four masking functions which can be defined on table column

  1. Default
  2. Email
  3. Partial
  4. Random

To understand each of these masking function let us create a Customer Table as shown in the following image by the following script:

Example Dynamic Data Masking Table
SCRIPT:

CREATE DATABASE SqlHintsDDMDemo
GO
USE SqlHintsDDMDemo
GO
CREATE TABLE dbo.Employee
(
 EmployeeId INT IDENTITY(1,1), Name NVARCHAR(100), DOJ DATETIME,
 EmailAddress NVARCHAR(100), Phone Varchar(15),	Salary INT
)
GO
INSERT INTO dbo.Employee (Name, DOJ, EmailAddress,Phone, Salary)
Values
 ('Basavaraj', '02/20/2005', 'basav@sqlhints.com',
   '123-4567-789',900000),
 ('Kalpana', '07/01/2015', 'kalpana@sqlhints.co.in',
   '123-4567-789',100000)
GO

Let us now understand one-by-one the dynamic data masking functions. These functions can be applied to columns during table creation or can be added to the existing table columns.

1. Default()

This dynamic data masking functions behavior is based on the data type of the column on which it is applied

  • For string types it shows X for each character and max it displays 4 X’s.
  • For numeric types it shows 0
  • For dates shows 1900-01-01 00:00:00.000

Let us apply the DEFAULT dynamic data masking function on the Name and DOJ columns of the Employee table by executing the following statement

---Add DEFAULT() masking function on the Name column
ALTER Table Employee
ALTER COLUMN NAME ADD MASKED WITH (FUNCTION='DEFAULT()')

---Add DEFAULT() masking function on the Name column
ALTER Table Employee
ALTER COLUMN DOJ ADD MASKED WITH (FUNCTION='DEFAULT()')

Let us create a new user and grant select permission on the Employee table by executing the following query.

--Create user reader
CREATE USER reader WITHOUT LOGIN
--Grant select permission to the user: reader
GRANT SELECT ON Employee TO reader

Let us try to fetch the records from the Employee table by executing the query in the context of this new user

EXECUTE AS USER = 'reader'
SELECT * FROM Employee
REVERT

RESULT:
Default Dynamic Data Masking Function

From the result we can see that Name column values are replaced by XXXX and DOJ column values are replaced by 1900-01-01 00:00:00.000 in the query result.

Grant UNMASK permission to the newly created user reader to allow viewing of the un-masked data by executing the following query.

--Grant Unmask permission to the user: reader
GRANT UNMASK TO reader

Now try re-executing the previously executed query to fetch the records from the Employee table in the context of the user reader

EXECUTE AS USER = 'reader'
SELECT * FROM Employee
REVERT

RESULT:
UnMask Permission Dynamic Data Masking

From the result we can see that now the reader user can see the un-masked or actual data of the masked columns Name and DOJ

Let us remove the UNMASK permission from the user reader by executing the following statement

--Remove Unmask permission from the user: reader
REVOKE UNMASK TO reader

2. Email()

This dynamic data masking function returns first character as it is and rest is replaced by XXX@XXXX.com.

Let us apply the EMAIL dynamic data masking function on the EmailAddress Column of the Employee table by executing the following statement

---Add Email() masking function on the Name column
ALTER Table Employee
ALTER COLUMN EmailAddress 
 ADD MASKED WITH (FUNCTION='Email()')

Let us try to fetch the records from the Employee table by executing the query in the context of the user reader

EXECUTE AS USER = 'reader'
SELECT * FROM Employee
REVERT

RESULT:
Email Dynamic Data Masking Function

From the result we can see that Email column values are replaced by first character as it is followed by XXX@XXXX.com in the query result.

Let us verify whether we can query a masked column value by the actual value. In the below example trying to fetch a employee record whose EmailAddress is kalpana@sqlhints.co.in in the context of the user reader

EXECUTE AS USER = 'reader'
SELECT * FROM Employee 
WHERE EmailAddress = 'kalpana@sqlhints.co.in'
REVERT

RESULT:
Where clause on masked column

3. Partial()

This dynamic data masking function provides a mechanism where we can reveal first and last few specified number of characters with a custom padding string in the middle.

partial (prefix ,padding , suffix)

Where: prefix is the starting number of characters to be revealed and suffix is the last number of characters to be revealed from the column value. Padding is the custom padding string in the middle.

Let us apply the PARTIAL dynamic data masking function on the Phone column of the Employee table by executing the following statement

ALTER Table Employee
ALTER COLUMN Phone 
 ADD MASKED WITH (FUNCTION='Partial(2,"-ZZZ-",2)')

Let us try to fetch the records from the Employee table by executing the query in the context of the user reader

EXECUTE AS USER = 'reader'
SELECT * FROM Employee
REVERT

RESULT:
Partial Dynamic Data Masking Function

From the result we can see that Phone’s first and last 2 characters are revealed in the masked result and in the middle it is padded by the string -ZZZ-.

4. Random()

This dynamic data masking function can be applied on a column of numeric type. It returns a random value between the specified ranges.

Let us apply the RANDOM dynamic data masking function with a random value range from 1 to 9 on the Salary column of the Employee table by executing the following statement

ALTER Table Employee
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION='Random(1,9)')

Let us try to fetch the records from the Employee table by executing the query in the context of the user reader

EXECUTE AS USER = 'reader'
SELECT * FROM Employee
REVERT

RESULT:
Random Dynamic Data Masking Function
Removing MASK definition from the Table Column

Below example shows how we can remove masked definition from the table column. Here in this example we are removing mask definition from the Phone column of the Employee table.

ALTER TABLE Employee 
ALTER COLUMN Phone DROP MASKED

Conclusion:

Dynamic Data masking provides a mechanism to mask or obfuscate the query result at the database level. The data stored in the data base is still in the clear or un-masked format. It is not a physical data encryption feature, an admin user or user with sufficient unmask permission can still see the complete un-masked data. This is a complementary security feature which is best-advised to use in-conjunction with other Sql Server Security features.

[ALSO READ]:

SQL SERVER 2016

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.

This article explains this new feature with simple and extensive list of examples.

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().

[ALSO READ] Dynamic Data Masking in Sql Server 2016

This feature is very useful in scenarios like below:

  • Shared Hosting/Multi-tenant scenario where common database used by multiple tenants to store the data. In such cases we want each tenant is restricted to access their data only.
  • In a traditional database, where we want to control the access to rows based on user’s role.

Parts of Row-Level Security
Parts of Row Level Security
Following are the three main parts of a Row-Level Security

  1. 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.
  2. Security Predicate
    Security Predicate is the one which binds a Predicate Function to the Table.

    There are two types of security predicates

    1. 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.
    2. 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.

  3. Security Policy
    Security policy is a collection of a Security Predicates which are grouped in a single new object called Security Policy.

[ALSO READ] New Features in Sql Server 2016

Enough theory let us jump on to the example which explains this theory in simple understandable term:

EXAMPLE: Let us take a scenario where we have a Cricket related database with Players table as shown in the below image. Assume that the players table has all the cricket playing countries team’s player data. When individual country cricket board application/user access this table data we want to return only the players belonging to that country.

Row Level Security Example

Script to create demo CRICKET database and Players table with sample player’s data

CREATE DATABASE CRICKET
GO
USE CRICKET
GO
CREATE TABLE dbo.Players
(
	PlayerId INT IDENTITY(1,1),
	Name	NVARCHAR(100),
	Country NVARCHAR(50),
	UserName sysname
)
GO
INSERT INTO dbo.Players (Name, Country, UserName)
Values('Sachin Tendulkar', 'India', 'BCCI_USER'),
	  ('Rahul Dravid', 'India', 'BCCI_USER'),
	  ('Anil Kumble','India', 'BCCI_USER'),
	  ('Ricky Ponting','Australia', 'CA_USER'),
	  ('Shane Warne','Australia', 'CA_USER')
GO

Execute the following statement to retrieve all the records from the Players table

SELECT * FROM dbo.Players

RESULT:
Row Level Security Example Table

Let us create three test user accounts by executing the following script

--Indian cricket board user
CREATE USER BCCI_USER WITHOUT LOGIN
-- Australian cricket board user 
CREATE USER CA_USER WITHOUT LOGIN    
--Admin user (International Cricket Council user)
CREATE USER ICC_USER WITHOUT LOGIN 

Let us execute the following script to grant read access on the Players table for the above three newly created users

GRANT SELECT ON Players TO BCCI_USER
GRANT SELECT ON Players TO CA_USER
GRANT SELECT ON Players TO ICC_USER

Let us create a Predicate function which returns 1 when user has access to the row by the following script. For this example we will use simple logic to determine the access to the row. The logic in this case is, if the name of the user who is executing the query matches with the UserName column value of that row or the name of the user who is executing the query is ICC_USER the user is allowed to access the row.

--Players predicate function
<pre class="brush: sql; gutter: false">
CREATE FUNCTION dbo.PlayersPredicateFunction
( @UserName AS SYSNAME )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN	SELECT 1 AS AccessRight
	WHERE @UserName = USER_NAME() OR USER_NAME() = 'ICC_USER'
GO

Let us create a Security Policy by executing the following script which is adding the above predicate function as a filter predicate on the Players table

--Security policy
CREATE SECURITY POLICY PlayersSecurityPolicy
--Security Predicate
ADD FILTER PREDICATE 
dbo.PlayersPredicateFunction(UserName) ON dbo.Players
WITH (STATE = ON)

Now execute the below statement to see whether I am (i.e. dbo owner) still able to fetch all the records from the Players table, which I was able to do prior to creating the security policy.

SELECT * FROM dbo.Players

RESULT
Row Level Security Post adding Security Policy

From the result it is clear that the filter predicate is filtering out all the rows as the SA user with which I am executing the query doesn’t have the access to any rows as per the predicate function definition. And below is the execution plan of this query:

Row Level Security Filter Predicate execution plan

From the execution plan we can see that the simple query SELECT * FROM dbo.Players is getting converted internally by the database engine as SELECT * FROM dbo.Players WHERE UserName = USER_NAME() OR USER_NAME() = ‘ICC_USER’ because of the Filter Predicate on the Players table

Execute the following query to see the rows to which the BCCI_USER user has access

EXECUTE AS USER = 'BCCI_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security BCCI_USER row access

From the result we can see that BCCI_USER with which the above query is executed can see only Indian cricket players as only these players UserName column value matches with the BCCI_USER.

Execute the following query to see the rows to which the CA_USER user has access

EXECUTE AS USER = 'CA_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security CA_USER row access

From the result we can see that CA_USER with which the above query is executed can see only Australian cricket players as only these players UserName column value matches with the CA_USER.

As per the filter predicate function definition the ICC_USER should be able to see all the Players rows. Let us confirm this by executing the following query.

EXECUTE AS USER = 'ICC_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security ICC_USER row access

Grant the DML operations permission on the Players table to the above three newly created users by executing the following statement

GRANT INSERT, UPDATE, DELETE ON Players TO BCCI_USER
GRANT INSERT, UPDATE, DELETE ON Players TO CA_USER
GRANT INSERT, UPDATE, DELETE ON Players TO ICC_USER

Let us execute the following statement to see whether the user BCCI_USER use who doesn’t have access to the Australian players rows can insert an Australian Player

EXECUTE AS USER  = 'BCCI_USER'
	INSERT INTO dbo.Players (Name, Country, UserName)
	Values('Glenn McGrath', 'Australia', 'CA_USER')
REVERT

Let us execute the following statement to see whether the BCCI_USER can see the newly inserted Australian player record by him

EXECUTE AS USER  = 'BCCI_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security BCCI_USER row access After Insert

From the result it is clear that BCCI_USER doesn’t have the access to the record Australian player record which he has inserted

Let us see whether the CA_USER can see the Australian player record which the BCCI_USER has inserted

EXECUTE AS USER  = 'CA_USER'
SELECT * FROM dbo.Players
REVERT

RESULT:
Row Level Security CA_USER row access After Insert

From the result we can see that the CA_USER has access to the Australian Player record which BCCI_USER has inserted.

So from the above example we can see that a FILTER predicate is not blocking the user from INSERTING a record which after insert is filtered by it for that user for any operation.

Don’t worry to avoid such behavior, we have Block predicate at our disposal. Let us now understand the Block predicate with examples:

BLOCK PREDICATE

Let’s add the AFTER INSERT BLOCK predicate on the Players table to block user from inserting a record which after insert user doesn’t have access to it.

Execute the below statement to alter the above Security policy to add the AFTER INSERT BLOCK predicate.

ALTER SECURITY POLICY PlayersSecurityPolicy
 ADD BLOCK PREDICATE dbo.PlayersPredicateFunction(UserName)
	 ON dbo.Players AFTER INSERT

Here for the AFTER INSERT BLOCK PREDICATE we are using the same predicate function which we have used to filter the records by the FILTER PREDICATE.

Basically, the AFTER INSERT BLOCK Predicate blocks user from inserting a record which after insert doesn’t satisfy predicate function. In other words from this example perspective the AFTER INSERT BLOCK predicate blocks the user from inserting a record which after insert user doesn’t have access to it.

Let us execute the following statement to see whether the user BCCI_USER who doesn’t have access to the Australian players rows can insert an Australian Player

EXECUTE AS USER  = 'BCCI_USER'
INSERT INTO dbo.Players (Name, Country, UserName)
Values('Adam Gilchrist', 'Australia', 'CA_USER')
REVERT

RESULT:

Msg 33504, Level 16, State 1, Line 2
The attempted operation failed because the target object ‘CRICKET.dbo.Players’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

From the result we can see that BLOCK Predicate is blocking a BCCI_USER user from inserting a record which after insert user doesn’t have access to it.

New Catalog Views/DMVs for the Row level security

Following are the two new catalog views introduced for Row level security in Sql Server 2016

  1. sys.security_policies
    This catalog view returns all the Security Policies in the database

    Execute the following statement to get all the security policies in the database with important security policy attributes/columns

    SELECT Name, object_id, type, type_desc,
    is_ms_shipped,is_enabled,is_schema_bound
    FROM sys.security_policies
    

    RESULT:
    Sys security_Policies Catalog views

  2. sys.security_predicates
    This catalog view returns all the Security Predicates in the database

    SELECT * 
    FROM sys.security_predicates
    

    RESULT:
    sys Security_Predicates Catalog View