VM Blogs

Fun with SQL – Find out numbers where adjacent digit differs by 1

noreply@blogger.com (Visakh Murukesan) — Sat, 02 May 2020 21:17:00 +0000

My friend and mentor Madhivanan had posted an interesting scenario on his blog

https://exploresql.com/2020/05/01/fun-with-sql-find-out-numbers-where-adjacent-digit-differs-by-1/

and gave it as a challenge for others

The question was below

I decided to have a try myself on this
Here;s my version of the query for the scenario

--Provide input value
declare @number int
set @number=105

;With numbers
as
(
select 1 as N UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
Num_matrix
AS
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 AS Seq
FROM numbers n1
cross join numbers n2
cross join numbers n3
cross join numbers n4
)

select m.Seq
from
(
select Seq
from num_matrix
where seq < @number
)m
cross join num_matrix n
where n.seq between 1 and len(m.Seq)
group by m.Seq
having count(*) = 1
or
sum(case when abs(substring(cast(m.seq as varchar(100)),n.seq,1)*1 - nullif(substring(cast(m.seq as varchar(100)),n.seq+1,1),'')*1) = 1
or abs(substring(cast(m.seq as varchar(100)),n.seq,1)*1 - nullif(substring(cast(m.seq as varchar(100)),n.seq-1,1),'')*1) = 1 then 0 else 1 end) = 0
order by m.Seq

And output is the below

Try your version and post back the query in comments

Happy coding!

SQLServer : Real Time Streaming RDBMS Data Using Apache Kafka Platform

noreply@blogger.com (Visakh Murukesan) — Mon, 03 Feb 2020 06:30:00 +0000

Problem Statement

There is an application database hosted in a SQLServer instance which is capturing data from a automation software including the readings as well as general health related details of multiple machines.

The requirement asks for the creation of a close to real time reporting platform which can be used as a monitoring and health check mechanism for the machine as well as perform time series based analysis on it.

Solution

The requirements like above requires the usage of a real time streaming platform which is capable of transmitting huge amount of data close to real time from the database. There are many such streaming platforms available.

One of the popular streaming tool which can be used in the above case is Apache Kafka. Apache Kafka is capable to connecting to variety of heterogeneous sources and transmit data from them in close to real time as streams.

So in our above case we make use Kafka to connect to the SQLServer database to extract data from tables and stream them in real time.

You can learn more about Kafka from the below link

https://kafka.apache.org/intro

Kafka Connect is an open source component of Kafka which provides the required framework to connect Kafka to a variety of systems to act as source or sink.

https://docs.confluent.io/current/connect/index.html

For connecting to SQL Server we shall make use of either of the below connectors

1. JDBC based connector

https://docs.confluent.io/current/connect/kafka-connect-jdbc/index.html

This connect directly to the tables and extract data from them

2. Debezium connector for SQLServer

https://debezium.io/documentation/reference/0.9/connectors/sqlserver.html

Debezium is a distributed platform which enables converting RDBMS data to event streams by means of set of source and sink connectors. The connectors enables Kafka Connect to systems as source to extract data and stream them as Kafka topics close to realtime. They also includes sink connectors which enables data from Kafka streams to be stored as data inside RDBMS table.

Illustration

A very detailed step by step illustration for the above scenario is explained in the latest article I've contributed to Technet community whose link is given below

https://social.technet.microsoft.com/wiki/contents/articles/53589.sql-server-real-time-data-streaming-using-apache-kafka.aspx

The article explains from setting up of the required systems (SQLServer, Kafka, Kafka Connect, Debezium connector) to streaming data and analyzing the stream through command line and through K-SQL server.

The article should help someone who is starting on the above mentioned systems and requires implementing use case similar to that specified above

Wish you happy reading.

As always feel free to give your valuables feedback

Graph Table Enhancements : Edge Constraints, Utility Functions

noreply@blogger.com (Visakh Murukesan) — Thu, 01 Aug 2019 11:31:00 +0000

The second part of the article https://visakhm.blogspot.com/2017/05/graph-tables-in-sql-2017.html can be found here

https://social.technet.microsoft.com/wiki/contents/articles/53162.graph-table-enhancements-edge-constraints-system-utility-functions.aspx

Here I have explained on the concept of Edge Constraints and how they can be used to enforce the relationships between Node tables within an Edge table.

The article also explains on certain system functions which can be used in conjunction with Node and Edge tables to extract the information from pseudo columns in Graph tables which can be utilized in the filter conditions of queries.

T-SQL Tips: Generating Nested XML Structures Efficiently Using FLOWR Expressions

noreply@blogger.com (Visakh Murukesan) — Wed, 10 Apr 2019 05:19:00 +0000

Introduction

I have been thinking about publishing an article on this for quite a while now. Of late have been too busy (or may be lazy is a better word!) to write on this. Finally thought of breaking the

Previously I had blogged about how FOR XML PATH can be used to generate nested XML structures in the below two articles

https://visakhm.blogspot.com/2014/05/t-sql-tips-fun-with-for-xml-path.html

https://visakhm.blogspot.com/2013/12/generating-nested-xml-structures-with.html

There is an alternate method that can be used to generate these nested XML structures using FLOWR expressions. This will be much more efficient than using nested subqueries for large data volumes.

Illustration

Using the same example as in the previous article, we can see how FLOWR expression can be applied to get same result

CREATE TABLE [dbo].[Orders](

[OrderID] [int] IDENTITY(1,1) NOT NULL,

[CustName] [varchar](100) NULL,

[OrderDate] [date] NULL,

[ReferredBy] [varchar](100) NULL,

[AgentGrp] [varchar](30) NULL,

PRIMARY KEY CLUSTERED

(

[OrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

SET IDENTITY_INSERT [dbo].[Orders] ON

INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (1, N'ABC Corp', CAST(0xB8350B00 AS Date), N'Agent 1', N'110')

INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (12353, N'R & K Associates', CAST(0x7A370B00 AS Date), N'Agent 5', N'105')

INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (13345, N'Zyng Enterprises', CAST(0x5D370B00 AS Date), N'Agent 3', N'110')

INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (15789, N'Maxim Bailey', CAST(0x7A370B00 AS Date), N'Agent 1', N'120')

INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (22345, N'Kyzer', CAST(0xA5370B00 AS Date), N'Agent 2', N'120')

INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (29398, N'ABC Corp', CAST(0x54370B00 AS Date), N'Agent 4', N'105')

SET IDENTITY_INSERT [dbo].[Orders] OFF

The result will be as below

As seen from result above the FLOWR expression helps us in building the XML in the structure we want.

The first part of the query using FOR XML will built a simple XML structure with all attributes involved. Then we apply query function on top of the simple XML we built and use FLOWR expressions inside to get XML to the format we desire

Another illustration is given below

declare @catalog table
(
catalogid int identity(1001,1),
catalogdescription nvarchar(100),
catalogcreationdate datetime
)

insert @catalog (catalogdescription,catalogcreationdate)
values
(N'mens wear',getdate()-120),
(N'womens wear',getdate()-35),
(N'sports wear',getdate()-90),
(N'accessories',getdate()-25),
(N'Beauté',getdate()-20)

declare @products table
(
ProductID int identity(10001,1),
ProductDesc nvarchar(100),
CatalogID int
)

insert @products (ProductDesc,CatalogID)
values ('Crop Tops',1002),
('Sweat Shirts',1002),
('Bodyfit Jeans',1001),
('Golden Perfurme',1005),
('Jerseys',1003),
('Pendant with Earstuds',1004),
('Anklet',1004),
('Shorts',1001)

declare @productattributeTypes table
(
AttributeTypeID int identity(10001,1),
AttributeTypeDesc nvarchar(100)
)

insert @productattributeTypes (AttributeTypeDesc)
values ('Shoe Size'),('Belt Size'),('Base Material'),('Color'),('Pattern'),('Size')

declare @productattributevalues table
(
AttributeID int identity(10001,1),
ProductID int ,
AttributeTypeID int,
AttributeValue nvarchar(100)
)

insert @productattributevalues (AttributeTypeID,ProductID,AttributeValue)
values (10003,10001,'Cotton'),
(10003,10002,'Polyester'),
(10006,10002,'XL'),
(10006,10003,'32'),
(10005,10003,'Slim fit'),
(10003,10004,'Cologne'),
(10006,10004,'100 ml'),
(10003,10005,'Polyester'),
(10003,10006,'Black Metal'),
(10003,10007,'White metal with stones'),
(10006,10008,'Cotton')

Here's the query

with the result as below

Conclusion

From the two illustrations above, its evident that FLOWR expression based method can be used effectively to generate nested XML structures

Artifacts

The full code for generating the XML structures can be found below

https://drive.google.com/file/d/1lUEv_6O5IgZJtQZgsH2odBzz9zW-kBit/view?usp=sharing

https://drive.google.com/file/d/17ugVWgTR9irRzeuf6ifHgzxz4GKxz_xH/view?usp=sharing

SQL Tips: String Or Binary Data Truncated Error Message Enhancement

noreply@blogger.com (Visakh Murukesan) — Mon, 29 Oct 2018 07:36:00 +0000

Introduction

I'm sure lots of us who have been developing in SQLServer over ages would agree to the fact that the error

String or binary data would be truncated.
The statement has been terminated.

is one of the most frustrating errors you would have ever come across in T-SQL. Especially in case of long stored procedures with lengthy INSERT...SELECT statements it was always a daunting to task to find the column which acted as the root cause for the above error. And most times the error happens at a later stage due to absence of any breaking data at the time of implementation.

A good majority of senior developers have always complained against this ambiguity and multiple connect items were logged for this issue which got pretty good support as well.

Implementation

When feedback platform was moved to feedback.azure.com Microsoft had opened a request in that for the connect requests and this had also got good number of votes

https://feedback.azure.com/forums/908035-sql-server/suggestions/32909539-string-or-binary-data-would-be-truncated

Accordingly MS started background work to fix this and finally the fix was released in SQL 2019 version to enhance the error message to include more information. Based on this, the error message has been modified to the below

String or binary data would be truncated in table 'XXXXXXXXXX', column 'YYYYYYY'. Truncated value: 'ZZZZZZ'.
The statement has been terminated.

This was really a good news for all of us, but still there was a small concern that we have wait a while to see this in action as most of the currently implemented instances were on SQL 2016 and SQL 2017.

But it seems MS read our mind on this and now I'm really happy to see the announcement that this enhancement has been backported to SQL 2017 CU 12 and on SQL 2016 SP2 CU.

To enable this currently a trace flag has also being introduced (trace flag 460) which can be enabled at session level or at server level itself. Once set it replaces the older error message with the new one above for truncation exceptions raised. The future SQL 2019 releases should have this message as the default and wont require setting the trace flag explicitly for this.

Illustration

Lets see an illustration of the above error message on a sample table

The code will look like below

--setting the trace flag for the session

DBCC TRACEON (460);

--sample table

declare @t table(

v varchar(10)

)

-- insert values

insert @t

values

('test'),

('this is a long value to check for truncation error') -- this raises the error

Now lets check the result

As expected we will get the new error message which gives clear indication of the table, column and value which caused the truncation exception. This makes it much easier for someone to debug and fix the issue.

Now if you turn off the trace flag and try, you can see that it reverts to the old error message

Conclusion

As seen from the above illustration, this new enhancement on truncation error is really a life saver for someone developing or debugging Transact SQL code and is sure to save many hours of development effort on truncation issues which is one of the high frequent issues we come across in ETL, datawarehousing projects.

The official announcement regarding the release can be found in the below link

https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/

Let me end this article by conveying big thanks to Pedro Lopes (@SQLPedro) – Senior Program Manager and the entire MS team for the help and support provided in addressing this issue and coming up with the release.

SQL Agent Tips: Using JOBID Token in T-SQL Job Steps To Fetch The Job GUID

noreply@blogger.com (Visakh Murukesan) — Tue, 16 Oct 2018 08:12:00 +0000

Introduction

One of the pretty cool features available in SQL Server Agent is the ability to use tokens for returning values for many system parameters at runtime (for example Server Name, Job Name, Database Name etc).
Recently there was an issue reported in the forums regarding the usage of JOBID token for getting the details of job within job step. This prompted me to analyse more on the feature.
This article explains how you can use the JOBID token to return the job identifier information inside the job step

Illustration

There are quite a few scenarios where we need to use job related information in scripts inside SQL Agent job steps. There are tokens available for this purpose which can be used to return data from job metadata.
One of common use case is when we need to capture the job id for logging purpose inside a step within the same job.
The code can be given as below

DECLARE @jobID varchar(100)

SET @jobID = '$(ESCAPE_SQUOTE(JOBID))'

EXEC [dbo].[notifyjobfailure]

@jobID

The notifyjobfailure procedure looks like this

CREATE proc [dbo].[notifyjobfailure]
@jobid varchar(100)
as
insert jobnotify(jobname)
select @jobid
GO

Where jobnotify is a simple table for capturing the job_id along with the current system time.

Include this within step of a job and try executing it. We will find that the job step will fail like below

As seen from the image above, we get a conversion error trying to use the token inside job step.

To understand why this is happening I tweaked the datatype of the column within the table and the parameter to be of type varchar.

So modified code will look like this

ALTER proc [dbo].[notifyjobfailure]
@jobid varchar(100)
as
insert jobnotify(jobname)
select @jobid

GO

Try executing the job and we can see that it executes fine now.
Check the table and we can see the below data populated

Checking the data we can see that the token value gets passed in hexadecimal format rather than as a GUID.
Taking this into consideration, we can tweak the code again to add a conversion step to ensure value gets passed as a valid unique identifier (GUID). Accordingly, the code will look like this

DECLARE @jobID uniqueidentifier

SET @jobID = CONVERT(uniqueidentifier,$(ESCAPE_SQUOTE(JOBID)))

EXEC [dbo].[notifyjobfailure]
@jobID

The procedure code will also be modified as below

CREATE proc [dbo].[notifyjobfailure]
@jobid uniqueidentifieras
insert jobnotify(jobname)
select @jobid

GO

Now go ahead and execute the job and you will find that it executes successfully

Conclusion

Based on the illustration above we can understand the below points

1. The value for JOBID token gets passed as a hexadecimal value
2. While trying to save the value to a table column of type uniqueidentifier always make sure we do an explicit conversion. Otherwise the code will break as seen from the illustration

SSIS Tips: Enforcing TLS 1.2 For SSIS 2012 Connections

noreply@blogger.com (Visakh Murukesan) — Tue, 14 Aug 2018 07:29:00 +0000

Impetus

The main inspiration behind this article comes from a recent issue faced in one of my projects for configuring TLS 1.2 based connectivity to a HTTP endpoint and steps taken in resolving the same

Scenario

In one of the projects, there was a SQL Agent job which started suddenly failing. There was no changes done on any of the core functionality so it was evident that the failure had something to do with some changes done at the destination end.The failure was attributed to a task which was utilizing a HTTP connection manager to connect to a URL for downloading response in XML format.

The failure message looked like below in the SQL Agent history

i.e.
Error: The underlying connection was closed: An unexpected error occurred on a send.
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host

On analysis and checking with the admin team, we came to know that they enabled TLS 1.2 on the server endpoint. This was causing the connection to fail as the SQLServer we had was on 2012 version and it was still using TLS 1.0 based connection.

The challenge was to see how this can be sorted out. This article discusses a quick solution which can be applied in scenarios like above to ensure successful connection

Solution

The solution involves forcing the connection to use TLS 1.2 this can be done by using the below single line of code inside a script task in your SSIS package.

This should be the first task inside the package and will have single line code as shown below

System.Net.ServicePointManager.SecurityProtocol = (System.Net.SecurityProtocolType)3072;

Here's how code looks inside the Script Task

3072 corresponds to TLS 1.2 protocol in the SecurityProtocolType enumeration within System.Net namespace

namespace System.Net
{
    [System.Flags]
    public enum SecurityProtocolType
    {
       Ssl3 = 48,
       Tls = 192,
       Tls11 = 768,
       Tls12 = 3072,
    }
}

And you need to have .Net framework 4.5 installed to get this work correctly.

Once this is done it enforces TLS 1.2 for the connection following and connections would be successful.

Conclusion

As shown above this method can be used in SSIS 2012 to ensure TLS 1.2 protocol is enforced for connecting to a client app which is using enhanced encryption

SSRS 2017 Tips: Solving Default Font Issue in SSDT 15.5 Preview

noreply@blogger.com (Visakh Murukesan) — Mon, 19 Feb 2018 08:13:00 +0000

Impetus

The impetus for writing this article came from a recent question which was asked in one of the forums.

This was regarding the creation of SSRS report using VS 2017 shell based SQLServer Data Tools. There was a post which indicated that preview tab of the SSDT 2017 causes some tablix data to disappear.
This article discusses on the root cause behind the issue and a workaround you shall use to avoid the issue.

Illustration

Consider the below sample report for this example which shows some addresses

Now lets try using this data inside a report.

The source query is below

select SUBSTRING(LTRIM(RTRIM(DestAddr4)),1,2) as Post,DestAddr1,DestAddr4,TransportTelNo from dbo.SvrDestinations
WHERE DestAddr4 is not null
and LEN(LTRIM(RTRIM(DestAddr4))) > 2
ORDER BY DestAddr4

Using this in a table will look like this

Now go to the preview tab to check the report preview and we can see this

Analysis

As you see from the screenshot, the data is missing in all the textboxes except the last one. This had us bewildered for a while and we started the analyzing the behavior to understand the root cause for this issue.
We tried exporting this to Excel and CSV and the screenshots are as below

The two screenshots showed that data was in fact available inside the tables which made it clear the issue was with the HTML viewer in the VS shell ( SSDT 15.5 Version)
Our next attempt was to use a matrix container instead of table container and see if the issue persists,
As per this, created a matrix container and added the same columns inside and the screenshot is as below

The original table and matrix is shown above as in the designer. The main table's visibility option is set to hide and matrix is set to visible. The behavior would be as below

As you see, we were able to display the data properly inside matrix. One thing to note here is that all columns are included inside grouping columns in the matrix with no values in detail columns (you can see the mandatory detail column blank in the last screenshot. Its column visibility would be set to hide for it to not generate the blank column)
From the above trials, the major things to note are

The data is not visible while displayed inside a table
The data is visible inside a matrix only when its inside a group.

Taking these things to consideration the next step was to check the report code behind

As you're probably aware, the code behind can be viewed by right clicking the report file (rdl) from solution and choosing view code. The code behind would be in XML format.

The code for the report looks like this

Fortunately I had another instance of SSRS in my machine which was of version 2012.

I compared code behind of a similar report to the above and found that in the current version we have an additional section on top which includes the font family information (highlighted in blue above).

I changed the font to Arial which was what my previous version of SSRS was using inside and tried running the report.
The report now looked like this

So as it turns out, the issue was with the default font family which was used inside SSDT.

Conclusion

As shown by the illustration above, SSDT 15.5 has an issue with preview mode not displaying full data correctly for tables and in matrix detailed rows.

The issue seems to be caused due to the default font (Segoe UI) being applied by the HTML preview screen. Changing the font seems to fix the issue. So whenever you face similar issue, try tweaking the font by opening the code behind to ensure its not a font based issue before you look for the other causes.

Hopefully this article could save you couple of hours of effort in fixing the issue caused due to font setting in the future.

References

The sample RDL along with table creation script can be downloaded from the below link

Report Link

SQL Tips: Demystifying FORMAT behavior in Datetime vs Time Values

noreply@blogger.com (Visakh Murukesan) — Wed, 08 Nov 2017 10:42:00 +0000

Introduction

Since SQL 2012 , one of the functions which I used quite regularly on my day to day coding has been the FORMAT function.
The major use case has been in formatting date and time based values to custom date and time formats. Though I rely upon front end to do the formatting on most occasions, sometimes we're required to do it at database layer as some tasks would be data feeds like Excel exports where formatted data has to be exported to Excel sheets.
This article clarifies the difference in the behavior of FORMAT function that you shall notice while working with datetime vs time based values

Scenario

Now lets set the scene for the topic on hand

Consider this small code snippet where I'm trying to format a standard datetime value to the format dd MMM yyyy hh:mm:ss AM/PM.

declare @dt datetime = '20171025 13:14:45'

SELECT FORMAT(@dt,'dd MMM yyyy hh:mm:ss tt')

The result is shown below

All is as expected and we are good

Now lets try using FORMAT function on a time value

declare @tm time = '14:34:56'

SELECT FORMAT(@tm,'hh:mm:ss tt')

And the result is as below

This was something totally unexpected. The expectation was to get the time based value like 02:34:56 PM

Explanation

I decided to do some analysis on FORMAT function to get to the bottom of this problem.

The official documentation of FORMAT function also clearly states supporting date, datetime, datetime2, time datatypes as seen from here

https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

But the catch is in the mapping table

If you check the documentation, time datatype alone is mapped to .Net datatype of TimeSpan while date, datetime, datetime2 are all mapped to DateTime datatype in .Net

The TimeSpan datatype accept custom specifiers as aid out by the article below

TimeSpan Accepted Format Specifiers

On contrast, the acceptable specifiers for DateTime are the below

DateTime Accepted Format Specifiers

Reading through the link you can note the below points regarding Format Specifiers

TimeSpan doesnt accept : as a valid separator whereas DateTime accepts : as a valid time separator
TimeSpan requires using \ as escape character to escape the : seperators
TimeSpan accepts only h or hh as specifier for hour part always returned in 24hr clock whereas DateTime accepts both lower and upper case (h,hh,H,HH) with lowercase giving time in 12h and uppercase in 24hr clock
DateTime accepts t or tt to return first or full part of AM/PM designator but TimeSpan doesnt support this as it always returns time in 24hr clock

Based on the above comparison we can see that for getting time value in hh:mm:ss format we need to use FORMAT function like this

declare @tm time = '02:34:56'

SELECT FORMAT(@tm,'hh\:mm\:ss')

And result is as shown

If you want to get format as 02:34:56 PM you need to cast it to datetime first before applying FORMAT function as per the stated points above

i.e like

declare @tm1 time = '02:34:56'

SELECT FORMAT(CAST(@tm1 as datetime),'hh:mm:ss tt')

And you shall get the intended result as shown

Conclusion

As seen from the illustration above, FORMAT function handles the datetime and time fields differently due to the difference in mapped .Net datatypes.

SQL Tips: Exploring Agent_Datetime Function

noreply@blogger.com (Visakh Murukesan) — Tue, 31 Oct 2017 09:50:00 +0000

Introduction

Recently there was a discussion in one of the forums which helped me to learn about a function which has been there for a long time in SQLServer and had eluded my knowledge till now.
This article explains on the details of the function along with the common use cases.

Agent_Datetime

The function of interest is agent_datetime.
It resides in the msdb database of a SQLServer instance and accepts two arguments. First argument would be an eight digit integer which represents date portion of a datetime value. The second argument would be a six digit integer representing the time part of the datetime value in hours,minutes and seconds format.

The function can be invoked as below

Select msdb.dbo.agent_datetime(20171027,231134)

The result would be as below

As you see it created a datetime value 2017-10-27 23:11:34.000 from the two integer values passed.

Background

There is a clear intention behind naming the function as Agent_Datetime and placing it within the msdb database.
The function is not officially documented and is basically used internally for converting the date and time values related to sql jobs from msdb system tables.

Use Cases

1. Get SQL Agent related datetime values from integer based columns in msdb catalog objects

There are few columns like for ex. rundate and runtime in sysjobhistory table which represented a date value stored as integer internally.
So query like below

SELECT j.name,dbo.agent_datetime(jh.run_date,jh.run_time) AS RunDate
FROM dbo.sysjobs j
JOIN dbo.sysjobhistory jh
ON jh.job_id = j.job_id

Will give you the list of jobs with their run dates in proper datetime format from msdb database.

2. Generic use to generate datetime values from integer based date and time fields

Though the function is created in msdb we're free to use it in our user databases to merge integer based date and time values to a proper datetime value.

As an illustration see the below

The above screenshot shows three different illustrations regarding use of agent_datetime function with different types of arguments.

The first example passes 8 digit date and 6 digit time based integer values and merges them into a proper datetime value.

The second examples shows what happens when you pass a 4 digit integer value for the time part instead of the expected 6 digits. The function will interpret it in the format mm:ss and returns merged date with the minute and seconds value. If you have only hour and minute parts make sure you pass it in hhmm00 format (i.e adding 0s for the missing seconds)

The final illustration depicts the case where you've only a date part to pass. In this case, you need to pass a default value of '' for the time part when you invoke the agent_datetime function. The function will return full datetime value with time part being default (00:00:00)

Conclusion

As seen from the illustrations above the msdb.dbo.agent_datetime scalar function can be used to generate proper datetime values from two separate integer based date and time values. It can be used for sql agent based catalog queries in msdb as well as for the queries in other databases.

The only caveat is that since its undocumented its better to refrain from using this on production code especially for the cases where we want the query to be run periodically.

For adhoc querying we shall use the function as a quick way to generate datetime values from integer date and time equivalents.

SQL 2017 Tips: Tweaking IDENTITY_CACHE Setting to Avoid IDENTITY Column Gaps

noreply@blogger.com (Visakh Murukesan) — Tue, 24 Oct 2017 11:24:00 +0000

Introduction

In my discussions with my fellow colleagues as well as on my online interactions with the developer community, I've come across a good amount of cases where people have raised a concern over the IDENTITY behavior in SQLServer.
As you probably know, IDENTITY based columns have a tendency to jump values whenever server restart occurs in the recent versions. Though this doesn't cause any problems regarding the sequence of values being generated, there are some use cases where I've seen this causing an issue for the application especially when the column is being expected to maintain the continuity in values and avoid gaps. Though its certainly a debatable topic on whether

Root Cause

In reality, the cause of this issue is purely intentional and by design.

The IDENTITY values are cached for improving the performance of insert operations on tables with IDENTITY columns. So if we had to dispense with this IDENTITY behavior our only option was to utilize the trace flag 272. This had to be set either using DBCC commands or using -T startup option.

SCOPED CONFIGURATION

SQL 2016 introduced SCOPED CONFIGURATIONs which allows configuration settings to be applied at a database instance level.
In SQL 2017 there was a new option added to it namely IDENTITY_CACHE which can be used for solving the above issue. Once set for a database, this setting ensures the identity values are not getting cached within the database. This will avoid the problem of IDENTITY column jumping values during the restart of a SQL Server.

Syntax

The scoped configuration setting for IDENTITY_CACHE can be enabled by using a statement as below

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Once above statement is executed the effect would be same as setting the trace flag 272 but at the database level.
This provides an added advantage to us due to the fact that we shall set it at the database level only for the databases where handling gaps in Sequence/Identity columns is a business problem and has to be avoided.
Wherever we need an optimal insert performance and cares little on the gaps in the values, we shall still keep the setting to the default which is ON where it maintains the cache for the values.

Conclusion

As explained above, the introduction of the new option in scoped configuration for controlling the identity cache behavior at the database level is very helpful in avoiding the identity value gaps issue in SQL 2017

References

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

SSIS Tips: Handling UTF 8 Based File Data

noreply@blogger.com (Visakh Murukesan) — Sat, 05 Aug 2017 11:02:00 +0000

Introduction

Quite a few times there were cases where we had to transfer CSV data containing non English based characters to SQLServer database. Though it seems straightforward there are few things we need to consider while working with CSV containing non English characters. This post explains the steps that shall be followed while transferring UTF 8 based data from a CSV onto a SQLServer based relational database system

Scenario

Consider the case where we've a CSV file with the below data

As you see from the above the file has a column which stores non English based data. The requirement was to get this data transferred to a SQLServer database for doing further manipulations with them.

Given this requirement our straightforward way would be to use a data flow task with a flat file source and an OLEDB destination to connect to the SQLServer db.

Lets see what happens when we try to use the standard DFT task for this case

Given below is the screenshot of the standard data flow task using non-unicode datatype I tried to use

As you see from the above it complains of data conversion issue between unicode and non-unicode data types.

Now lets try using an unicode datatype and see what happens

Now we get an error stating that it cant convert between the two code pages. This clearly implies we cant use the default OLEDB destination in the case of UTF - 8 based file data.

Now lets try using the next possible option i.e. with ADO .Net destination.

The data flow for this case is as below

The column will be of unicode datatype (nchar, nvarchar) in the database.

Once executed you can check the table to see that data is getting populated correctly

As seen from the above results data got transferred correctly this time to the SQLServer table.

Conclusion

By virtue of the above illustration, its clear that for transferring UTF-8 based file data to SQLServer using SSIS we need to do the below

1. Use UTF-8 based codepage (65001) in the Flat File connection manager

2. Use ADO .Net destination task inside data flow task

3. Use a Unicode based datatype for the table column like nchar, nvarchar

References

ADO .Net destination

Code Page Flat Files

SQL 2017 Tips: Avoiding Gaps in IDENTITY, SEQUENCE Based Column Values

noreply@blogger.com (Visakh Murukesan) — Tue, 20 Jun 2017 03:40:00 +0000

Scenario

One of the common conversations I've come across in a typical work day is as below

Emp X: I just found that the IDENTITY column values for couple of tables have jumped to a new range
Emp Y: Can you check if there was any server restart?
Emp X: Oh yes! I see it now... There was a planned server restart at night.

The reason for this behavior is explicit.

Server restart causes identity based columns to skip values. This is due to the fact that identity columns pre-allocates (caches) next set of values and on server restart the cache gets flushed. Once server comes back it resumes at the next value from the maximum value in the cache which creates gaps in identity sequence. This behavior started from 2012 version onwards.

This blog discusses a new enhancement available in SQL 2017 CTP which can be used to solve the above issue

Solution

This behavior was explained to be by design and proposed solution was to use trace flag 272 which will allow the functionality to continue as per earlier version. The MS connect link which discusses this is given below

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

For setting this we had to go to SQLServer configuration manager and set trace flag 272 in the startup parameters as discussed here

http://sqlblog.com/blogs/kalen_delaney/archive/2014/06/17/lost-identity.aspx

In SQL 2017, we have a good news. Microsoft has included this as a new option in SCOPED CONFIGURATION options for the database. The option is called IDENTITY_CACHE and is included in the documentation below

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

This has two advantages over the previous trace flag based method

1. We can set it from management studio itself rather than setting it as a startup option from SQL Configuration Manager.

2. The most important advantage is that we can scope it to database level rather than setting it at the instance level. This makes it possible for us to have other databases co-existing in the same server instance with new behavior enabled i.e. with gaps.

The complete syntax for the feature is as below

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Once you set it at a database level, the server restart doesn't cause any gaps in the IDENTITY objects in the database, which is in line with the behavior before SQL 2012 version.

This small but very useful enhancement will certainly help the developers to set IDENTITY value behavior in a database as per their requirement.

SQL 2017 Tips: Creating Table On The Fly Onto A Specific FileGroup

noreply@blogger.com (Visakh Murukesan) — Fri, 16 Jun 2017 07:27:00 +0000

Introduction

One of the frequently used and the most popular features in SQLServer is the ability to create a table on the fly based on a query. This has saved my day on numerous occasions. Only thing I was missing in this was the ability to organize the table into its own filegroup of my choice

Why are filegroups necessary?

There are various advantages of organizing tables into separate filegroups, the main among them being the below

For large tables it makes sense to move them to a separate filegroup
In case of tables where data size is almost same as non-clustered index size, we shall consider keeping table in one file group and index on another
Organizing tables based on portfolio so that tables can be easily migrated / archived if required.
Maintaining some data which is read only along with read write data in same db. In this case we can keep these tables in a separate read only file group

The Solution

The SQL 2017 CTP2 release includes a solution to the above limitation.

The SELECT ... INTO syntax for creating the table is now extended to include the filegroup information.
The modified syntax would be like below

SELECT column1, column2,.. columnN
INTO NewTableName
ON FileGroupName
FROM Table1 t1
JOIN Table2 t2
ON condition...
...

If you see the code above, the highlighted part designates the filegroup to which this new table has to be associated. Only caveat is that the filegroup must pre-exist in the database where the above query is being executed otherwise SQLServer will throw an error

Illustration

Lets see how we can create a new table on the fly and associate it to a newly created filegroup on the database

Steps are as outlined below

1. Adding a new filegroup to existing database Test2017

ALTER DATABASE [Test2017] ADD FILEGROUP [SupplimentaryDataFG]

You can check the db properties to ensure this is created as per the below

2. Associating a new file to the newly created filegroup

ALTER DATABASE [Test2017]

ADD FILE

(

NAME = test1dat3,

FILENAME = 'D:\SupplData.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP [SupplimentaryDataFG];

The file will get created as per below

3. Creating a test table on the fly on the newly created filegroup

SELECT * INTO TestTable
ON SupplimentaryDataFG
FROM
(VALUES
('Test',1),
('Foo',2),
('Foobar',3)

)t(Description,Code)

Checking the table properties to see the filegroup info

Another way to check this is to use the script as option in SSMS. Script as CREATE option for the table will give the below code

USE [Test2017]

/****** Object: Table [dbo].[TestTable] Script Date: 6/16/2017 7:09:03 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[TestTable](

[Description] [varchar](6) NOT NULL,

[Code] [int] NOT NULL

) ON [SupplimentaryDataFG]

The highlighted part clearly shows the table to be in the new filegroup we created and associated it to

Conclusion

From the above illustration its pretty evident that we can create a table on the fly and associate it to any filegroup we have pre-created in the database.

This is a very useful small enhancement which has been introduced in SQL 2017 which helps us to organize the table data into multiple filegroups simultaneously at the time of their creation on the fly using queries.

References

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql

SSRS Tips: Creating 2012/2014 BI Templates Using VS 2015

noreply@blogger.com (Visakh Murukesan) — Wed, 03 May 2017 07:26:00 +0000

Introduction

With the release of VS 2015 shell, its now possible to utilize the BI templates available init for creating SSRS reports, SSIS packages and SSAS projects compatible with server versions 2012 till 2016 from the same unified environment.

This quick post discusses on two things that you need to consider while creating projects using VS 2015 shell which are targeted to deploy at a lower version server.

Scenario

I had recently used VS 2015 shell for development of SSRS reports and SSIS packages for one of my projects. Our production server was still on 2014 version. As per official documentation of Microsoft VS 2015 shell can be used for creating packages, reports etc which can be deployed onto 2012, 2014 or 2016 versions. The link below has the details

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

As per the above I used SSDT 2015 for creating SSRS reports. After creating the reports I tested locally and once I was happy I decided to deploy the reports to the server which was in 2014 version.

For this I went to Project Properties and tweaked the below properties

My expectation was that by setting this property I would be able to deploy the reports to 2014 server. But to my surprise I got the below message when I tried to deploy the rdl file

The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas

Solution

I didn't understand why this was happening and did some analysis on other settings to see if there's anything I could find. Fortunately i was able to spot the below property

This comes under Tools options and determines the Server Version to which report is to be deployed. The default value set was 13.0 which corresponds to SQL 2016. Once I changed it to 12.0 and tried deploying, the report got successfully deployed to the server.
Something to note next time when you do deployment of SSRS,SSIS or SSAS project from VS 2015 shell to make sure deployment is successful.

Conclusion

Hope this quick tip will save you sometime in avoiding the above specified issue while deploying BI projects using VS 2015 shell templates

SQL 2017 CTP Released!

noreply@blogger.com (Visakh Murukesan) — Thu, 20 Apr 2017 06:14:00 +0000

SQLServer 2017 CTP is out for public preview now.
SQLServer 2017 has some exciting features on board like resumable online index rebuild, adaptive query query processing etc
Also great improvement can be seen in the performance of datawarehouse workloads in Linux environment.

More details on the release here

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/19/sql-server-2017-community-technology-preview-2-0-now-available/

SSIS Tips: Case Study - Extracting ZenDesk data using SSIS

noreply@blogger.com (Visakh Murukesan) — Thu, 13 Apr 2017 05:33:00 +0000

Impetus

Its been a long time since I wrote a blog.
The main reason was that I've been super busy with some exciting projects of late.
One of my recent projects had an interesting requirement which I thought would be worth sharing for the benefit of the wider audience.

Scenario

One of my recent BI engagements with a e-commerce customer had a use case which required customer satisfaction metrics to be captured and analyzed. The metrics were to be derived from variety of data including support tickets, delivery, feedback and notification emails etc. The client was using ZenDesk as the ticketing application. All of the above required information was tracked by means of tickets within ZenDesk so the attempt was to integrate ZenDesk data using ETL tool SSIS and use the data for analysis along with the other available data.

Illustration

ZenDesk offers API access to its data. The attempt was to use SSIS and access the required ZenDesk data using API calls.

For the purpose of this blog lets see a simple scenario of how SSIS can be used to call ZenDesk API for extracting tickets related data.

The details regarding ZenDesk APIs can be found here

https://developer.zendesk.com/rest_api/docs/core/introduction

Refer to the Search section which explains on various API endpoint for searching ZenDesk data. We will be utilizing this for searching and fetching the ticket information using our required filters. The search API is flexible due to the fact that it allows a set of query string parameters which can be utlized to customize our search. Some example usages are given in the below link

https://developer.zendesk.com/rest_api/docs/core/search

So for our case since we require ticket information the full URL would look like below

https://{subdomain}.zendesk.com/api/v2/search.json?sort_by=created_at&sort_order=asc&query=created> type:ticket

Here subdomain represents the subdomain used while registering with ZenDesk (mostly the company name) and date value the date relative to which you want to do the search. sort_by parameter is used to get the search result in chronological order.

The response for the above API call will look like this

{

"results":[

{

"url":"https://.zendesk.com/api/v2/tickets/36046.json",

"id":36046,

"external_id":null,

"via":{

"channel":"email",

"source":{

"from":{

"address":"xyz2@gmail.com",

"name":"XYZ 2"

"to":{

"name":"abc.com",

"address":"customercare@abc.com"

"rel":null

}

...],

"facets":null,

"next_page":null,

"previous_page":null,

"count":72

}

As you see it gives a JSON response having the above structure

One more thing to note here is that API returns only a maximum of 100 results in a response. In case search result exceeds 100 items the response gets auto-paged at every 100 results. The url for the next page will be sent along with the response (refer to the next_page/previous_page elements in the above JSON)

So the cases where we have more results we need to capture next_page value each time and sent a new request to the server using that url. This will continue until the last page in which case the next_page value will be null.

Considering all these points a simple package for extracting ZenDesk data will look like below

Now we can analyse the various tasks inside the package to understand their purpose

For Loop: The loop is included to ensure we iterate through requests until we reach the last page of our search result. It will be a simple For loop based on a counter which will be initialized to 1 and reset inside the loop when the results are completed to break from the iteration.

The Expression Tasks are used to set the iterative values for request URL i.e. URL to fetch next page of results as well as for the reset of the URL and counter values

The script task does the download of search results in JSON format into a text file using API call. It utilizes a HTTP connection which will use ZenDesk API URL as the source through a variable expression to pass dynamic values like date.
The variable expression in our case looks like below

"https://{domain}.zendesk.com/api/v2/search.json?sort_by=created_at&sort_order=asc&query=created >" + (DT_WSTR, 30) (DT_DBDATE) @[User::Date] + " type:ticket"

The script task code will look like this

Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)

Dim connection As New HttpClientConnection(nativeObject)

' Download the file #1
' Save the file from the connection manager to the local path specified
Dim filename As String = ""
connection.DownloadFile(filename, True)

The Execute SQL Task uploads the JSON data from the file to a table in SQL Server database. Once data is transferred it uses a JSON parsing logic to get the individual elements out.
Parsing JSON can be done using native SQL functions like OPENJSON, JSON_VALUE etc if you're on SQL 2016 . This is already explained in my earlier blogs below

http://visakhm.blogspot.in/2016/07/whats-new-in-sql-2016-native-json.html

http://visakhm.blogspot.in/2016/07/whats-new-in-sql-2016-native-json_13.html

If you're on an earlier version of SQL, you need to use a UDF for parsing JSON like one below

https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

The SP code will look like below in that case

INSERT JSONData(JSON)

SELECT *

FROM OPENROWSET(BULK '',SINGLE_BLOB) AS t

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

SELECT f.*

INTO #Temp

FROM JSONData j

CROSS APPLY dbo.parseJSON(j.JSON)f

SELECT @NextURL = StringValue

FROM #Temp

WHERE NAME = 'next_page'

Once you parse it you will get values of various elements within the JSON. This can be used for extracting the required information

The next_url value will be captured and returned through an OUTPUT parameter back to the package. Using this we can determine whether to iterate again for the next page of results or break the loop.

Once you parse the data out to a table you can then use it for your further manipulations like aggregation etc.

Conclusion

As seen from the above illustration you can use a simple script task utilizing a HTTP connection in SSIS package to call and extract data through ZenDesk APIs. This data can then be used for manipulations within your applications and can be utilized in BI reports, dashboards etc.

WinSCP Tips: Passing Credentials With Special Characters

noreply@blogger.com (Visakh Murukesan) — Fri, 21 Oct 2016 09:08:00 +0000

Scenario

Recently there was an issue faced in one of our projects on a file processing system that we designed. The file processing system was designed to process third party files which had to be downloaded from a FTP location.

We designed a SSIS package to do the FTP download and file processing and used WinSCP for the purpose. The logic used was as per one of my earlier articles as given below

http://visakhm.blogspot.ae/2012/12/implementing-dynamic-secure-ftp-process.html

On executing the package the Execute Process Task which was added to do the FTP has failed. As SSIS doesn't give too much information on the error I put a break point on the pre execute event of the execute process task and captured the full statement which is to be executed by WinSCP.

I launched a command prompt window and tried executing the statement generated by the SSIS and got the below

As seen from the above screenshot I got an access denied error. I checked the credentials again and tried them on WinSCP client installed in my machine and to my surprise it was working fine allowing me to connect to the FTP location. From this I could infer that the problem was with the way SSIS or the WinSCP command line is handling the credentials.

I did some detailed analysis on the command used for FTP operation by WinSCP which was as below

open ftp://username:password@Servername:Port

And managed to find the issue. The problem in this case was the password which contains special characters like +. This will break in case of the WinSCP.

Solution

The solution in the above case was to encode the special characters before being passed to the url. If you've used .NET programming before you should know that there's a function available in .NET EscapeDataString which is available in System.Uri namespace. This function provides with the encoded values when special character is passed.

Based on this we can develop a solution such as below for handling the above scenario.

Create a function which will parse through the passed URL string and looks for special characters within it. Once found it will pass the character to the EscapeDataString function which will return the encoded value corresponding to it.
The string parsing can be done using RegexMatch function to find out the special characters.
So the overall package would look like this

The Script task will receive as parameter the URL for FTP/SFTP and then parse it for encoding the special characters as below

public void Main()
{

var str = EncodeUrl(Dts.Variables["Url"].Value.ToString());
Dts.Variables["EncodedUrl"].Value = str;
Dts.TaskResult = (int)ScriptResults.Success;
}

private static string EncodeUrl(string URLInput)
{
const string pattern = "[$&+,/:;=?@]";
var match = Regex.Match(toEncode, pattern);
while (match.Success)
{
URLInput = URLInput.Replace(match.Value, Uri.EscapeDataString(match.Value));
match = Regex.Match(URLInput, pattern);
}
return URLInput;
}

Once the encoded URL is returned by the above function you can then store it in a variable and use it to set expression properties for your Execute Process Task to use the returned value.

In the above code you need to add a reference to System.Text.RegularExpressions namespace before using the Regex.Match function. Uri.EscapeDataString comes under System.Data namespace which will be included by default inside the script task.

In the above case function will replace character + with its encoded value %2B which will work well with the WinSCP.

Conclusion

As discussed above the special characters in credentials have to be encoded before generating the URL for FTP in case of applications like WinSCP.
The above illustration gives an easier way of doing it within the SSIS package using a simple .NET function which can be invoked from within a simple script task.
Hope this helps to solve similar issues faced due to the presence of special characters in URLs and in query string parameter values.

SSRS Tips : Fixing Format Issues in MHTML Rendered SSRS Reports

noreply@blogger.com (Visakh Murukesan) — Fri, 26 Aug 2016 08:42:00 +0000

Impetus

The impetus for writing this blog came from a recent issue I faced while setting up email based subscription for one of my MIS reports for enterprise wide distribution. The report content had to be embedded within the email in HTML format so I had set the rendering option as MHTML for the subscription. When report is delivered using subscription it can be noticed that none of the formatting, cosmetic settings on the original report gets applied.

This article explains how you can overcome this issue to preserve the native format settings you applied to the report while rendering it through automated subscriptions in MHTML format.

Illustration

Consider the below report created in SSRS

The requirement was to schedule an automated delivery of the above report through email to a list of users. The report has to be embedded within the email body rather than being as a separate attachment.
The best way to implement the above requirement is to set an email based subscription for the report and configure the render format as MHTML. This would ensure that the report will be embedded within the body of the email
As per the above approach subscription is configured for the report as below

.

When the subscription got triggered the email obtained was as below

As you see from the above, the rendered report was stripped off its format settings when rendered in MHTML format

This would cause difficulty in interpreting the report especially when amount of data shown in the report is huge. Question was how to make sure the format remains intact while rendering through sunscription as above

I was able to arrive at the solution after some analysis. The solution is to use expression mode to set the format properties for the report cells

As an example see the below illustration

Once you set this as an expression and set it for subscription you can see the rendered report as below

As you see from the above the report gets rendered with all the format settings correctly applied. All the property values have been set using expressions in the report designer and MHTML format will preserve the values while rendering through the subscription.

Conclusion

As seen from the illustration above, the format properties of the report can be preserved intact by using expressions while rendering in MHTML format.

What's New in SQL 2016 - Temporal Tables

noreply@blogger.com (Visakh Murukesan) — Tue, 02 Aug 2016 06:04:00 +0000

Introduction

Most of us are familiar with CDC tables in SQL Server. CDC or Change Data Capture is used to track and capture the DML changes happening in a table and makes use of log entries for capturing the information. SQL 2016 provides similar kind of functionality for capturing the history of the data related to memory optimized tables. These historical data capturing feature is referred to as Temporal Tables.

Memory Optimized Tables were introduced in SQLServer 2014 and they provide an efficient way to store and manipulate data using natively compiled procedures, They provide two levels of durability Schema Only and Schema and Data. Schema Only will only make sure schema is preserved in case of a server restart whereas in the latter case both the schema as well as the data is persisted as in the case of a normal table (disk based).
Temporal tables are only supported in the case where durability option is set to schema and data. These tables will be persisted to disk along with data. When we created a temporal table it will preserve the history and will provide data easily based on our point in time analysis.
The temporal tables makes use of a history table internally to track the history of data changes happening in the memory optimized table. The main table will have two datetime2 type fields which are referred to as period columns. This is used by the table to determine the validity of each record to provide an effective point in time analysis. The date fields determine the validity of a record ie period for which record is(was) valid

Illustration

Consider the below case where we have a database to which we are going to add a memory optimized table and make it a temporal table.

For creating a memory optimized table we need to first have a file group in the database that supports memory optimized data. This can be done by using a series of ALTER DATABASE statements as shown below (Assumption is that SQL2016LocalTest is an already existing database in your SQLServer instance)

ALTER DATABASE [SQL2016LocalTest] ADD FILEGROUP mem_opt CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [SQL2016LocalTest] ADD FILE (name='mem_opt1', filename='') TO FILEGROUP mem_opt
ALTER DATABASE [SQL2016LocalTest] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO

Once the filegroup is setup as above we shall create the memory optimized table on it using CREATE TABLE statement as below

CREATE TABLE dbo.HotelBooking
(
BookingID int,
RoomNo int,
CustomerName varchar(100),
BookingDate datetime,
ValidFrom datetime2 generated always as row start not null default ('19000101'),
ValidTo datetime2 generated always as row end not null default ('99991231'),
PRIMARY KEY CLUSTERED (BookingID),
period for system_time(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.BookingHistory));

Once the above statement is executed we will have a temporal table setup with an internal history table which will used for tracking the history of data from the main table.
You can check this by expanding tables under the corresponding databases node to see the tables as per below

As you see the table will be represented with a small clock icon indicating that it is a temporal table and will also have the history table specified under it. The history table will be an exact replica of the main table in schema and will have clustered index on the key column.
Now lets insert some data to this table. For evaluating the history capture its best if you do the data manipulation operation over a period of time.

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1001,
101,
'Peter',
'20150923'
)

UPDATE dbo.HotelBooking
SET CustomerName = 'Ramu'
WHERE RoomNo = 101

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1002,
115,
'John',
'20160314'

)

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1005,
212,
'Mary',
'20160416'

)

UPDATE dbo.HotelBooking
SET BookingDate = '20160420'
WHERE BookingID = 1005

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1011,
307,
'Khader',
'20160617'

)

UPDATE dbo.HotelBooking

SET CustomerName = 'Michelle'
WHERE RoomNo = 101

INSERT dbo.HotelBooking
(
BookingID,
RoomNo,
CustomerName,
BookingDate
)
VALUES
(
1015,
115,
'Mohsin',
'20160622'

)

DELETE FROM dbo.HotelBooking
WHERE BookingID = 1002

Once you've done the above data addition/modification you can check the main and history table and you will get the below data snapshot

I have done the data modification over a period of time (around 2 days) to indicate the effect on history table.
If you analyze the above data you can see how the progression of data is getting captured in the history
Everytime a new insert happens it will be captured in the main table HotelBooking with ValidFrom set to the corresponding system time and ValidTo set to the maxdate value (9999-12-31 23:59:59.99999)
Similarly every update operation is carried out by means of two internal operations a delete of the old entry followed by insertion of new entry. This can be seen from resultset above where you will have an entry in both the tables (see records with BookingID 1001 and 1005 in the above case). The history table will have the entry with the values prior to the modification with ValidFrom as actual ValidFrom value from the main entry and ValidTo as the corresponding system time. The main table entry will have the modified values with ValidFrom as the corresponding system time and ValidTo as the max date value.
In the case of delete operations the record will be removed from the main table and there will be an entry added to the history table with ValidFrom as the original ValidFrom and ValidTo as the corresponding system time.
Now that we have got an idea of what happens on the background and how data will get captured in the two tables lets now see some functions which are associated to Temporal tables and which will help us to query temporal data for time based analysis.

AS OF time

AS OF time function gives the snapshot of temporal table data at an instant of time i.e the data that is valid at that instance

Lets try it on our sample table and illustration is below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME AS OF '2016-07-25 05:15'

Now see the last resultset and compare it with the original table vs history entries and you will notice that there are only 3 entries in the resultset.
If you check the ValidFrom and ValidTo values for the resultset entries its pretty evident that those were the entries which were valid as on specified snapshot time (ie ValidFrom < 2016-07-25 05:15 < ValidTo)
The ignored records were those which were either expired before snapshot date (ex: 1001 Peter) or the ones which became valid after the snapshot time (ex: 1015,1011)
So effectively what AS OF function does is to apply a query logic as below

SELECT * FROM dbo.HotelBooking WHERE '2016-07-25 05:15' BETWEEN ValidFrom AND ValidTo
UNION ALL
SELECT * FROM dbo.BookingHistory WHERE '2016-07-25 05:15' BETWEEN ValidFrom AND ValidTo

Execute this and you will get the same resultset as returned by AS OF

Graphically this can be depicted using the three scenarios as shown below

FROM time1 TO time2

FROM time1 TO time2 function returns all records whose validity overlaps the period specified by time1 to time2.

The illustration for this can be shown as below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME FROM '2016-07-24 10:34' TO '2016-07-25 05:16:55.4510553'

The resultset in this consists of all records with the interval ValidFrom to ValidTo overlapping between interval specified by time1 and time2. One thing to note here is that record with BookingID 1011 was not included as it became valid only from the end boundary value whereas function considers only the cases where interval starts or ends within the boundary value ie the equivalent query can be written as

SELECT * FROM dbo.HotelBooking WHERE ValidFrom < '2016-07-25 05:16:55.4510553' AND ValidTo > '2016-07-24 10:34'

UNION ALL

SELECT * FROM dbo.BookingHistory WHERE ValidFrom < '2016-07-25 05:16:55.4510553' AND ValidTo > '2016-07-24 10:34'

The corresponding scenarios can be represented graphically as below

BETWEEN time1 AND time2

BETWEEN function works quite similar to the FROM function to get the resultset where record interval of ValidFrom to ValidTo overlaps with the interval specified by time1 to time2. The only difference between BETWEEN and FROM is that BETWEEN considers the end boundary value (time2) also as a part of the interval unlike the FROM function.

So if you see the illustration for BETWEEN it looks like below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME BETWEEN '2016-07-24 10:34' AND '2016-07-25 05:16:55.4510553'

Check the result and you can see that its very much similar to resultset for FROM with only addition being the record with BookingID 1011 which starts at the same time as the end time of the interval
So equivalent query in this case can be given as

SELECT * FROM dbo.HotelBooking WHERE ValidFrom <= '2016-07-25 05:16:55.4510553' AND ValidTo > '2016-07-24 10:34'
UNION ALL
SELECT * FROM dbo.BookingHistory WHERE ValidFrom <= '2016-07-25 05:16:55.4510553' AND ValidTo > '2016-07-24 10:34'
ORDER BY ValidFrom

Corresponding graphical representation is shown below

CONTAINED IN (time1,time2)

CONTAINED IN function returns a resultset where the record validity falls within the interval from time1 to time2 ie ValidFrom and ValidTo both lying within the interval

Illustration is given below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME CONTAINED IN ( '2016-07-24 10:32','2016-07-25 05:18:35.3157322') ORDER BY ValidFrom

The resultset on analysis reveals that only those records whose validity falls fully within the interval time1 to time2 are returned by CONTAINED IN function.

The equivalent query is as below

SELECT * FROM dbo.HotelBooking WHERE ValidFrom >= '2016-07-24 10:32' AND ValidTo <= '2016-07-25 05:18:35.3157322'
UNION ALL
SELECT * FROM dbo.BookingHistory WHERE ValidFrom >= '2016-07-24 10:32' AND ValidTo <= '2016-07-25 05:18:35.3157322' ORDER BY ValidFrom

Graphically this can be represented as below

ALL

The ALL function simply combines the results from both the main and history table in the resultset

Illustration below

SELECT * FROM dbo.HotelBooking FOR SYSTEM_TIME ALL ORDER BY ValidFrom

The resultset includes all records from both the tables

The equivalent query is as below

SELECT * FROM dbo.HotelBooking

UNION ALL

SELECT * FROM dbo.BookingHistory ORDER BY ValidFrom

See the corresponding graphical representation below

Conclusion

The above illustrations gives a clear idea about how temporal functions can be used to generate time based results from a temporal table.

This shows that Temporal tables provide a much convenient way of capturing history and using it for effective time based analysis
Hope you this article was informative enough. Feel free to revert with any comments you may have.

Whats New in SQL 2016 - Native JSON Function Support - Performing Data Manipulations in JSON Documents

noreply@blogger.com (Visakh Murukesan) — Wed, 13 Jul 2016 17:01:00 +0000

This is the last and the final article in the series which discusses on the native JSON support in SQL 2016.
Previously I had blogged on how JSON documents can be generated from relational data using FOR JSON construct here

http://visakhm.blogspot.ae/2016/06/whats-new-in-sql-2016-native-json.html

and also how JSON documents can be parsed and relational data can shred from them using OPENJSON here
http://visakhm.blogspot.ae/2016/07/whats-new-in-sql-2016-native-json.html

The article explains on some of the JSON functions which is available in Transact SQL and can be utilized for manipulating the data within JSON documents.

ISJSON()

The ISJSON function is used to check if a document passed has a valid JSON data. It accepts an expression which would be a string and validates if it contains aproper JSON data. The return value would be a boolean result (1 or 0) based on whether the string contains a valid JSON result or not.

Lets see the small illustration below to understand the ISJSON function

declare @x nvarchar(max)='{
"Items": [
{
"ItemID": 101,
"ItemDesc": "Monitor CRT 18\"",
"Unitprice": 200
},
{
"ItemID": 110,
"ItemDesc": "Printer Catridge",
"Unitprice": 112
},
{
"ItemID": 112,
"ItemDesc": "Copier Ink",
"Unitprice": 20
},
{
"ItemID": 123,
"ItemDesc": "Wireless Mouse",
"Unitprice": 30
}
]
}'

SELECT ISJSON(@x)

The result will be 1 as @x contains valid JSON data

If at all the passed document can be considered as a valid JSON, ISJSON function returns 1 as seen from the below examples

SELECT *
FROM
(
VALUES ('{}'),('[]'),('{"item":1}'),('{"q":[1]}'),('[0,1,2]')
)t(v)
WHERE ISJSON(v)=1

and the result

The first two values represents blank JSON object and array documents whereas the next three represents JSON document with single object/array object and all of them are valid.
One more thing you may note from the above illustration is how we can use ISJSON function as a filter condition in the WHERE clause to check for the rows with valid JSON data in the column and then to do some manipulations on them.

JSON_VALUE()

JSON_VALUE is another very useful utility function which can be used to parse a JSON document and return a single element value. JSON_VALUE takes as argument a JSON expression and a path and traverses the JSON document as per the path to return a single scalar value.

Lets see a simple example of how JSON_VALUE can be applied to get values from a JSON document

declare @x nvarchar(max)='{
"Item":
{
"ItemID": 101,
"ItemDesc": "Monitor CRT 18\"",
"Unitprice": 200
}
}'

The above shows a very simple JSON document with a single JSON element having three key value pairs.
The values from the above JSON document can be parsed out using JSON_VALUE function as per below code

SELECT JSON_VALUE(@x,'$.Item.ItemID') AS ItemID,
JSON_VALUE(@x,'$.Item.ItemDesc') AS ItemDesc,
JSON_VALUE(@x,'$.Item.Unitprice') AS Unitprice
The result is as below

This is a simple document with all key values in the same level. Now lets see how we can use JSON_VALUE in the case where JSON document has key values in multiple levels

Consider the below JSON example

declare @x nvarchar(max) = '{
"Order": {
"OrderDesc": "Order 1",
"OrderDate": "2013-02-12T00:00:00",
"ShippedDate": "2013-02-20T00:00:00",
"WebOrder": false,
"OrderItems":
{
"OrderQty": 10,
"Item": {
"ItemDesc": "Wireless Mouse",
"Unitprice": 30,
"SUbItem": {
"SubItemDesc": "SP17"
}
}
}
}'

Now to get the data from the various levels within the document we can apply JSON_VALUE function as per below

SELECT JSON_VALUE(@x,'$.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'$.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'$.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'$.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'$.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'$.Order.OrderItems.Item.ItemDesc') AS ItemDesc,
JSON_VALUE(@x,'$.Order.OrderItems.Item.Unitprice') AS Unitprice,
JSON_VALUE(@x,'$.Order.OrderItems.Item.SUbItem.SubItemDesc') AS SubItemDesc

The corresponding result is given below

One thing to note here is that JSON_VALUE can return only a scalar value from within a single key value pair in the JSON data. So in case you're referring to a JSON array object which includes multiple key value pairs as per the path specified it will return NULL under the default mode.
This can be illustrated using the below example

declare @x nvarchar(max) = '{
"Order":
{
"OrderDesc": "Order 1",
"OrderDate": "2013-02-12T00:00:00",
"ShippedDate": "2013-02-20T00:00:00",
"WebOrder": false,
"OrderItems":
{
"OrderQty": 10,
"Item": [
{
"ItemDesc": "Wireless Mouse",
"Unitprice": 30,
"SUbItem": [
{
"SubItemDesc": "SP17"
},
{
"SubItemDesc": "SP22"
}
]
},
{
"ItemDesc": "Monitor",
"Unitprice": 22,
"SUbItem": [
{
"SubItemDesc": "SP27"
}
]
}
]
}
}
}'

SELECT JSON_VALUE(@x,'$.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'$.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'$.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'$.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'$.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'$.Order.OrderItems.Item.ItemDesc') AS ItemDesc,
JSON_VALUE(@x,'$.Order.OrderItems.Item.Unitprice') AS Unitprice,
JSON_VALUE(@x,'$.Order.OrderItems.Item.SUbItem.SubItemDesc') AS SubItemDesc

If you try the above illustration the result obtained will be something like below

Analyze the result and you will find that the code is not able to retrieve any keys at the Item and SUbtem level using JSON_VALUE function.
If you check the JSON document carefully you can see that Item and SUBItem consists of actually an array object (notice the [] around them) rather than a single JSON element. This is why JSON_VALUE cant extract value from them. As previously explained the JSON_VALUE can only extract a single scalar value which is why it fails in the above scenario.
For the above case if we want to get the values we need to tweak the query as below to get the Item and SUbItem level key values

The modified query would be like this

SELECT JSON_VALUE(@x,'$.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'$.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'$.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'$.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'$.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'$.Order.OrderItems.Item[0].ItemDesc') AS ItemDesc1,
JSON_VALUE(@x,'$.Order.OrderItems.Item[0].Unitprice') AS Unitprice1,
JSON_VALUE(@x,'$.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc') AS SubItemDesc11,
JSON_VALUE(@x,'$.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc') AS SubItemDesc12,
JSON_VALUE(@x,'$.Order.OrderItems.Item[1].ItemDesc') AS ItemDesc2,
JSON_VALUE(@x,'$.Order.OrderItems.Item[1].Unitprice') AS Unitprice2,
JSON_VALUE(@x,'$.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc') AS SubItemDesc21

And the result below

But this method is not scalable as we have to be certain on the number of elements at query time to apply the position number based static logic. In such cases we would require logic which will iterate through the JSON elements to return corresponding key values. This will be explained in detail later in this article.

strict and lax modes

There are two modes under which the path expression works in the case of JSON functions.

The default is the lax mode where the parser doesn't enforce strict checking for the JSON path specified. In case the path is valid it returns the value as per the path. Otherwise it will return a NULL value. lax mode being the default option you dont need to explicitly specify it within the path. So all the previous examples uses lax mode under the hood.

In contrast strict mode the JSON path passed is strictly checked against and in case it is an invalid path it returns an error.
To illustrate this lets use a modified version of the last query in the two modes and see the difference

lax mode

SELECT JSON_VALUE(@x,'lax $.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'lax $.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'lax $.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'lax $.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'lax $.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[0].ItemDesc') AS ItemDesc1,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[0].Unitprice') AS Unitprice1,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc') AS SubItemDesc11,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc') AS SubItemDesc12

JSON_VALUE(@x,'lax $.Order.OrderItems.Item[1].ItemDesc') AS ItemDesc2,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[1].Unitprice') AS Unitprice2,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc') AS SubItemDesc21,
JSON_VALUE(@x,'lax $.Order.OrderItems.Item[1].SUbItem[1].SubItemDesc') AS SubItemDesc22

and the result

Notice the NULL value for the nonexistent key coming at the end. This query works without any error because of lax mode
Now lets see what happens with the same query in the strict mode

SELECT JSON_VALUE(@x,'strict $.Order.OrderDesc') AS OrderDesc,
JSON_VALUE(@x,'strict $.Order.OrderDate') AS OrderDate,
JSON_VALUE(@x,'strict $.Order.ShippedDate') AS ShippedDate,
JSON_VALUE(@x,'strict $.Order.WebOrder') AS WebOrder,
JSON_VALUE(@x,'strict $.Order.OrderItems.OrderQty') AS OrderQty,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[0].ItemDesc') AS ItemDesc1,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[0].Unitprice') AS Unitprice1,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[0].SUbItem[0].SubItemDesc') AS SubItemDesc11,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[0].SUbItem[1].SubItemDesc') AS SubItemDesc12,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[1].ItemDesc') AS ItemDesc2,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[1].Unitprice') AS Unitprice2,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[1].SUbItem[0].SubItemDesc') AS SubItemDesc21,
JSON_VALUE(@x,'strict $.Order.OrderItems.Item[1].SUbItem[1].SubItemDesc') AS SubItemDesc22

Now the result

As seen from the above example it throws an error when it finds an non existent key as per the specified path in the strict mode.

JSON_QUERY()

JSON_QUERY function works similar to JSON_VALUE with the exception that it accepts a JSON path and returns a JSON document as the result

See the below example to understand how JSON_QUERY works

declare @x nvarchar(max) = '{

"Order":

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItems":

{

"OrderQty": 10,

"Item":

{

"ItemDesc": "Wireless Mouse",

"Unitprice": 30,

"SUbItem":

{

"SubItemDesc": "SP17"

}

SELECT JSON_QUERY(@x,'$.Order.OrderItems') AS OrderItems,

JSON_QUERY(@x,'$.Order.OrderItems.Item') AS Item,

JSON_QUERY(@x,'$.Order.OrderItems.Item.SUbItem') AS SubItem

The result will contain three JSON documents as shown below

JSON_QUERY function similar to JSON_VALUE works in two supported modes lax and strict with lax being the default.

The functionality remains the same with strict mode throwing an error in case of passing an invalid path as compared to lax mode returning a NULL value

Here is an illustration on the same

declare @x nvarchar(max) = '{

"Order":

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItems":

{

"OrderQty": 10,

"Item":

{

"ItemDesc": "Wireless Mouse",

"Unitprice": 30,

"SUbItem":

{

"SubItemDesc": "SP17"

}

SELECT JSON_QUERY(@x,'lax $.Order.OrderItems') AS OrderItems,

JSON_QUERY(@x,'lax $.Order.OrderItems.Item') AS Item,

JSON_QUERY(@x,'lax $.Order.OrderItems.Item.SUbItem') AS SubItem,

JSON_QUERY(@x,'lax $.Order.Customer') AS Customer

Result is as given below

Now strict mode will be as below

SELECT JSON_QUERY(@x,'strict $.Order.OrderItems') AS OrderItems,

JSON_QUERY(@x,'strict $.Order.OrderItems.Item') AS Item,

JSON_QUERY(@x,'strict $.Order.OrderItems.Item.SUbItem') AS SubItem,

JSON_QUERY(@x,'strict $.Order.Customer') AS Customer

with the result

Parsing JSON Array Objects And Nested Levels

Now lets look at how JSON array objects and JSON documents with nested levels can be parsed.

Both the functions JSON_QUERY and JSON_VALUE can only parse a single JSON element at a time. Hence when we have to have to use a construct which helps us to iterate through each object in the array before we apply JSON_VALUE or JSON_QUERY over it.

If you have read my earlier article on JSON support you would be able to make out that we can use OPENJSON construct to iterate through the JSON document array.

Once OPENJSON is applied it returns as a elements of JSON as a resultset over which we can apply the JSON_VALUE or the JSON_QUERY function to return us a single scalar value or a JSON element depending on the case.

Now lets see an illustration to understand this

Consider the example given below

declare @x nvarchar(max) = '{

"Orders": [

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItems": [

{

"OrderQty": 10,

"Item": [

{

"ItemDesc": "Wireless Mouse",

"Unitprice": 30,

"SUbItem": [

{

"SubItemDesc": "SP17"

}

]

}

]

{

"OrderQty": 8,

"Item": [

{

"ItemDesc": "Copier Ink",

"Unitprice": 20,

"SUbItem": [

{

"SubItemDesc": "SP5"

{

"SubItemDesc": "SP12"

}

]

}

]

}

"Customer": [

{

"CustomerDesc": "KBC Associates"

}

]

{

"OrderDesc": "Order 2",

"OrderDate": "2015-06-24T00:00:00",

"ShippedDate": "2015-06-30T00:00:00",

"WebOrder": false,

"OrderItems": [

{

"OrderQty": 12,

"Item": [

{

"ItemDesc": "Printer Catridge",

"Unitprice": 112,

"SUbItem": [

{

"SubItemDesc": "SP1"

{

"SubItemDesc": "SP3"

}

]

}

]

}

"Customer": [

{

"CustomerDesc": "ABC inc"

}

]

{

"OrderDesc": "Order 5",

"OrderDate": "2016-05-17T00:00:00",

"ShippedDate": "2016-05-22T00:00:00",

"WebOrder": true,

"OrderItems": [

{

"OrderQty": 28,

"Item": [

{

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

}

]

}

"Customer": [

{

"CustomerDesc": "AAM & Co"

}

]

}

]

If you analyze the above example you can see that JSON document consist of array of JSON objects each with nested JSON array objects within them. Since it involved JSON array objects we would require OPENJSON function to parse them. Once we get individual JSON elements we can then apply JSON_VALUE or JSON_QUERY over them

So the query will look like this

SELECT JSON_VALUE(value,'$.OrderDesc') AS OrderDesc,

TRY_CONVERT(datetime,JSON_VALUE(value,'$.OrderDate')) AS OrderDate,

TRY_CONVERT(datetime,JSON_VALUE(value,'$.ShippedDate')) AS ShippedDate,

JSON_VALUE(value,'$.WebOrder') AS WebOrder,

JSON_VALUE(oi,'$.OrderQty') AS OrderQty,

JSON_VALUE(i,'$.ItemDesc') AS ItemDesc,

JSON_VALUE(i,'$.Unitprice') AS UnitPrice,

JSON_VALUE(si,'$.SubItemDesc') AS SubItemDesc,

JSON_VALUE(c,'$.CustomerDesc') AS CustomerDesc

FROM OPENJSON(@x,'$.Orders') t

CROSS APPLY (SELECT JSON_QUERY(t.value,'$.Customer[0]')) m(c)

CROSS APPLY (SELECT value FROM OPENJSON(t.value,'$.OrderItems'))n(oi)

CROSS APPLY (SELECT value FROM OPENJSON(oi,'$.Item'))p(i)

OUTER APPLY (SELECT value FROM OPENJSON(i,'$.SUbItem'))q(si)

The resultset for the query is as given below

As seen from the above case, for iterating through JSON array we make use of OPENJSON. Within the array each individual JSON document can be fetched using JSON_QUERY function. In the above case since there is only single customer element we retrieve it using JSON_QUERY function and then apply series of JSON_VALUE function calls to return each key value within it. For the other documents which holds an array object we use further correlated OPENJSON calls by using APPLY operator and for each of the returned JSON documents we apply JSON_VALUE functions to return individual key element values.

One thing to note here is that path being passed is case sensitive so you need to make sure you pass path in the exact case as how it appears within the JSON document

If you want a filter to be applied on above query you can make use of JSON_VALUE to make sure you retrieve only the documents where scalar value returned matches a given condition

Like for example

SELECT JSON_VALUE(value,'$.OrderDesc') AS OrderDesc,

TRY_CONVERT(datetime,JSON_VALUE(value,'$.OrderDate')) AS OrderDate,

TRY_CONVERT(datetime,JSON_VALUE(value,'$.ShippedDate')) AS ShippedDate,

JSON_VALUE(value,'$.WebOrder') AS WebOrder,

JSON_VALUE(oi,'$.OrderQty') AS OrderQty,

JSON_VALUE(i,'$.ItemDesc') AS ItemDesc,

JSON_VALUE(i,'$.Unitprice') AS UnitPrice,

JSON_VALUE(si,'$.SubItemDesc') AS SubItemDesc,

JSON_VALUE(c,'$.CustomerDesc') AS CustomerDesc

FROM OPENJSON(@x,'$.Orders') t

CROSS APPLY (SELECT JSON_QUERY(t.value,'$.Customer[0]')) m(c)

CROSS APPLY (SELECT value FROM OPENJSON(t.value,'$.OrderItems'))n(oi)

CROSS APPLY (SELECT value FROM OPENJSON(oi,'$.Item'))p(i)

OUTER APPLY (SELECT value FROM OPENJSON(i,'$.SUbItem'))q(si)

WHERE JSON_VALUE(oi,'$.OrderQty') > 20

This will only retrieve the data for the case where specified key value condition is satisfies within the JSON element and we will get the below output

JSON_MODIFY()

JSON_MODIFY function as the name implies allows us to modify the data within an existing JSON document. This function can be used in cases where we need to replace an existing value or set a new value to a key within JSON document. We can also use it to rename a given JSON document key.

The syntax of JSON_MODIFY us as below

JSON_MODIFY(JSONexpression, JSON Path, value)

Lets now see JSON_MODIFY function in action

Consider this simple example

declare @x nvarchar(max) = '{

"Order": {

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItems":

{

"OrderQty": 10,

"Item": {

"ItemDesc": "Wireless Mouse",

"Unitprice": "30",

"SUbItem": {

"SubItemDesc": "SP17"

}

Now lets try to update value of Unitprice key in Item to 50 using JSON_MODIFY

The code will look like this

SELECT JSON_VALUE(@x,'$.Order.OrderItems.Item.Unitprice') AS Unitprice

SET @x = JSON_MODIFY(@x,'$.Order.OrderItems.Item.Unitprice',50)

SELECT @x,JSON_VALUE(@x,'$.Order.OrderItems.Item.Unitprice') AS Unitprice

The corresponding result will be as below

As seen from the result the key value got updated to 50

Now lets see someother applications of JSON_MODIFY.

As specified earlier JSON_MODIFY can also be used to create a new key within JSON element, append a value to existing object array, rename a key within a JSON element or delete a key within JSON_MODIFY.

Lets see some illustrations below

1. Create a new key to JSON element

SET @x = JSON_MODIFY(@x,'$.Order.OrderItems.Item.ItemQty',50)

SELECT @x

The result is as below

{
   "Order":{
      "OrderDesc":"Order 1",
      "OrderDate":"2013-02-12T00:00:00",
      "ShippedDate":"2013-02-20T00:00:00",
      "WebOrder":false,
      "OrderItems":{
         "OrderQty":10,
         "Item":{
            "ItemDesc":"Wireless Mouse",
            "Unitprice":"30",
            "SUbItem":{
               "SubItemDesc":"SP17"
            },
            "ItemQty":50
         }
      }
   }
}

2. Append a value to JSON element

declare @x nvarchar(max) = '{

"Order": {

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItems":

{

"OrderQty": 10,

"Item": {

"ItemDesc": ["Wireless Mouse"],

"Unitprice": "30",

"SUbItem": {

"SubItemDesc": "SP17"

}

SET @x = JSON_MODIFY(@x,'append $.Order.OrderItems.Item.ItemDesc','Optical USB Enabled')

SELECT @x

The resulting JSON document is as below

{
   "Order":{
      "OrderDesc":"Order 1",
      "OrderDate":"2013-02-12T00:00:00",
      "ShippedDate":"2013-02-20T00:00:00",
      "WebOrder":false,
      "OrderItems":{
         "OrderQty":10,
         "Item":{
            "ItemDesc":[
               "Wireless Mouse",
               "Optical USB Enabled"
            ],
            "Unitprice":"30",
            "SUbItem":{
               "SubItemDesc":"SP17"
            }
         }
      }
   }
}

As seen above it appends the value passed to the ItemDesc array object.

3. Rename a key within JSON document

Now lets see an example of the case where we need to rename a key within a JSON array

SET @x = JSON_MODIFY(JSON_MODIFY(@x,'$.Order.OrderItems.Item.SubItem',JSON_QUERY(@x,'$.Order.OrderItems.Item.SUbItem')),'$.Order.OrderItems.Item.SUbItem',NULL)

SELECT @x

The result as below

{
   "Order":{
      "OrderDesc":"Order 1",
      "OrderDate":"2013-02-12T00:00:00",
      "ShippedDate":"2013-02-20T00:00:00",
      "WebOrder":false,
      "OrderItems":{
         "OrderQty":10,
         "Item":{
            "ItemDesc":[
               "Wireless Mouse"
            ],
            "Unitprice":"30",
            "SubItem":{
               "SubItemDesc":"SP17"
            }
         }
      }
   }

Analyse the result and you can see that the JSON document key element has been renamed from SUbitem to SubItem.

4. Delete a key within JSON document

declare @x nvarchar(max) = '{

"Order":{

"OrderDesc":"Order 1",

"OrderDate":"2013-02-12T00:00:00",

"ShippedDate":"2013-02-20T00:00:00",

"WebOrder":false,

"OrderItems":{

"OrderQty":10,

"Item":{

"ItemDesc":[

"Wireless Mouse"

"Unitprice":"30",

"ItemQty":50,

"SubItem":{

"SubItemDesc":"SP17"

}

The code is as given below

SET @x = JSON_MODIFY(@x,'$.Order.OrderItems.Item.ItemQty',NULL)

SELECT @x

The result will look like this

{
   "Order":{
      "OrderDesc":"Order 1",
      "OrderDate":"2013-02-12T00:00:00",
      "ShippedDate":"2013-02-20T00:00:00",
      "WebOrder":false,
      "OrderItems":{
         "OrderQty":10,
         "Item":{
            "ItemDesc":[
               "Wireless Mouse"
            ],
            "Unitprice":"30",
            "SubItem":{
               "SubItemDesc":"SP17"
            }
         }
      }
   }
}

If you notice the result you will see that the ItemQty key is missing in the document which indicates that key has been deleted from the document.

Please note that all the above queries are using the default lax mode. While using strict mode it will throw the standard error indicating JSON path not found if you pass a non existing key name.

Conclusion

The examples given above illustrate the options available in SQL 2016 using standard JSON functions to do the data manipulations within the JSON data.

One or more of these functions can be effectively used in cases where data within the JSON needs to be modified to get the desired result.

Whats New in SQL 2016 - Native JSON Function Support - Parsing JSON Document to Shred Relational Data

noreply@blogger.com (Visakh Murukesan) — Fri, 08 Jul 2016 09:33:00 +0000

Previously I had blogged about the native support for JSON in SQL 2016 and the ability to generate JSON documents from relational data using FOR JSON syntax here .
This blog is next in the series on JSON support and explains about how you can use T-SQL code to parse and shred out data from existing JSON documents to load data to relational database tables.

Illustration

Consider the below JSON document

declare @x nvarchar(max)='{

"Item":{

"ItemID": 101,

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

}}'

Now lets see how we can shred out the data from this simple JSON element

We will make use OPENJSON function available in SQL 2016 for this purpose.

The basic syntax for OPENJSON is as below

OPENJSON(jsonexpression,path)

OPENJSON accepts a JSON document expression as the first argument and parses it based on path specified within the second argument

So in the above case we will write the query as below to parse the JSON document

SELECT *
FROM OPENJSON(@x,'$.Item')

The output will look like below

key value type

---- -------- ---------

ItemID 101 2

ItemDesc Monitor CRT 18" 1

Unitprice 200 2

This will be the standard format in which output is returned. If you analyze the result you will see that it consists of three columns first one giving the key names inside JSON document, second column giving the values corresponding to the keys and third one giving the data types of the values. The full list of type values along with the corresponding data types can be seen in the below link

https://msdn.microsoft.com/en-us/library/dn921885.aspx

If you want the get the Key names as columns along with their corresponding values in the output you can use the optional WITH clause along with OPENJSON to transpose the result

The query in that case would look like this

SELECT *
FROM OPENJSON(@x,'$.Item')

WITH

(

ItemID int,

ItemDesc varchar(50),

Unitprice int

)

And result would be the below

ItemID ItemDesc Unitprice

------------ ----------------- ------------------

101 Monitor CRT 18" 200

Parsing JSON Array

Now lets see what happens in the case of document with an array of JSON values

declare @x nvarchar(max)='{

"Items": [

{

"ItemID": 101,

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

{

"ItemID": 110,

"ItemDesc": "Printer Catridge",

"Unitprice": 112

{

"ItemID": 112,

"ItemDesc": "Copier Ink",

"Unitprice": 20

{

"ItemID": 123,

"ItemDesc": "Wireless Mouse",

"Unitprice": 30

}

]

SELECT *

FROM OPENJSON(@x,'$.Items')

WITH

(

ItemID int,

ItemDesc varchar(100),

Unitprice int

)

The above query will give you this result

The above examples shows how you can use OPENJSON to parse JSON documents to obtain relational data.

Nested JSON Documents

Now lets see what happens in the case where there are nested JSON documents. In such case a single OPENJSON wont be sufficient to extract the whole data.

Consider the below document as an example

declare @json nvarchar(max)='{

"Orders": [

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItems": [

{

"OrderQty": 10,

"Item": [

{

"ItemDesc": "Wireless Mouse",

"Unitprice": 30,

"SubItem": [

{

"SubItemDesc": "SP17"

}

]

}

]

{

"OrderQty": 8,

"Item": [

{

"ItemDesc": "Copier Ink",

"Unitprice": 20,

"SubItem": [

{

"SubItemDesc": "SP5"

{

"SubItemDesc": "SP12"

}

]

}

]

}

"Customer": [

{

"CustomerDesc": "KBC Associates"

}

]

{

"OrderDesc": "Order 2",

"OrderDate": "2015-06-24T00:00:00",

"ShippedDate": "2015-06-30T00:00:00",

"WebOrder": false,

"OrderItems": [

{

"OrderQty": 12,

"Item": [

{

"ItemDesc": "Printer Catridge",

"Unitprice": 112,

"SubItem": [

{

"SubItemDesc": "SP1"

{

"SubItemDesc": "SP3"

}

]

}

]

}

"Customer": [

{

"CustomerDesc": "ABC inc"

}

]

{

"OrderDesc": "Order 5",

"OrderDate": "2016-05-17T00:00:00",

"ShippedDate": "2016-05-22T00:00:00",

"WebOrder": true,

"OrderItems": [

{

"OrderQty": 28,

"Item": [

{

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

}

]

}

"Customer": [

{

"CustomerDesc": "AAM & Co"

}

]

}

]

The above is an example of a JSON document with nested JSON elements going till 4th level. In such a case we need to use series of nested OPENJSON constructs to get data from JSON document as column data.

The query will look like this

SELECT t.OrderDesc,

t.OrderDate,

t.ShippedDate,

t.WebOrder,

oi.OrderQty,

c.CustomerDesc,

i.ItemDesc,

i.Unitprice,

si.SubItemDesc

FROM OPENJSON(@json,'$.Orders')

WITH (

OrderDesc varchar(20),

OrderDate datetime,

ShippedDate datetime,

WebOrder bit,

OrderItems nvarchar(max) AS JSON,

Customer nvarchar(max) AS JSON

CROSS APPLY OPENJSON(OrderItems,'$')

WITH

(

OrderQty int,

Item nvarchar(max) AS JSON

)oi

CROSS APPLY OPENJSON(Customer,'$')

WITH

(

CustomerDesc varchar(100)

CROSS APPLY OPENJSON(Item,'$')

WITH

(

ItemDesc varchar(100),

Unitprice decimal(30,2),

SubItem nvarchar(max) AS JSON

OUTER APPLY OPENJSON(SubItem,'$')

WITH

(

SubItemDesc varchar(50)

)si

And you will get the output as below

If you analyze the query you can see that it uses a series of nested calls to OPENJSON to parse out the data at different levels within the JSON document.

Each level it applies the WITH option to return the individual element values as well as returns the next level elements in JSON format itself using AS JSON option which is then parsed by next OPENJSON call by using CROSS or OUTER APPLY to pass the JSON value as a correlated parameter to the OPENJSON. This is continued till the last level to return the complete set of values from within each of JSON documents at the different levels. Depending whether you want a perfect match or not you can use CROSS or OUTER as the option for the APPLY operator.

JSON Path vs XPath

If you compare the json path expressions used in the above examples against xpath expression you use for shredding XML data you can see that the basic format is very similar. Here we use $ to denote the root element as compared to / in case of XML. Also each child member is represented using . as compared to / in XML. So anyone who has experience in writing XML XPath expressions in SQLServer will find it easy to follow JSON path expressions

Conclusion

The above illustrations shows how OPENJSON can be used to parse JSON and shred data into relational tables.

This method can be used to get data in JSON format from the applications and then parse and shred the data to populate the tables.

How to solve OPENQUERY MySQL Update Issue Using ODBC DSN

noreply@blogger.com (Visakh Murukesan) — Tue, 05 Jul 2016 18:45:00 +0000

This blog explains an issue you're likely to face while executing a remote UPDATE query against a MySQL server over a linked server connection using ODBC DSN based connectionstring

Scenario

Recently I was working in a project where I required data to be transferred from a remote server and do some manipulations with it. The status column in the remote server table have to be updated later. The remote server in this case was MySQL server and we were using ODBC DSNs based connectionstring.
When we try to execute an update statement against the remote server as below

UPDATE t
SET col1 = value1,
col2 = value2
FROM OPENQUERY(LINKEDSERVERNAME,'select col1,col2,col3 from mysqlserver.tablename') t
WHERE col3 = value3

We got an error message as given below

OLE DB provider "MSDASQL" for linked server "MYLINKEDSERVER" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "MYLINKEDSERVER" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

Solution

We tried tweaking the update query but still was not able to overcome the error
I searched regarding the error and finally managed to get the solution from a forum.
The solution required tweaking a small setting in the ODBC DSN property as shown below

Once we set the Return matched rows instead of affected rows option the UPDATE query executed successfully.
This setting will allow the updating of values in MySQL to the fields which has a value already.

Conclusion

From the above illustration we can see that this setting will prevent the error happening in remote query updates due to optimistic concurrency setting.

Hopefully this should help someone who is facing the same issue to get around the issue.

Whats New in SQL 2016 - Native JSON Function Support - Generating JSON documents using FOR JSON clause

noreply@blogger.com (Visakh Murukesan) — Mon, 27 Jun 2016 15:32:00 +0000

One of the most sought after feature that got included in SQL 2016 is the native support for JSON data in Transact SQL. JSON has become kind of de-facto standard for data transfer in web applications these days. As such it makes it really awesome to know that you can generate JSON directly from the relational database using native SQL querying.
SQL 2016 has introduced FOR JSON clause which can be used to generate JSON documents from relational database tables.
For people who have already worked with XML data it would be much easier as the syntax would be mostly similar to that of XML functions.
This blog article is the first in the series on the native JSON support in SQL 2016 and explains on how JSON documents can be generated from relational tables using the FOR JSON construct.

Illustration

For the sake of this article consider the below table

declare @Items table

(

ItemID int,

ItemDesc varchar(100),

Unitprice decimal(10,2)

)

insert @Items

values(101,'Monitor CRT 18"',200),

(110,'Printer Catridge',112),

(112,'Copier Ink',20),

(123,'Wireless Mouse',30)

There are two main options we can use with FOR JSON statement for constructing JSON documents.

1. FOR JSON PATH

The PATH option of the FOR XML construct provides flexibility to generate the JSON documents in the hierarchy we require.

Basic Syntax

Now to convert this relational data to JSON format we can use a simple query like below

SELECT *

FROM @Items

FOR JSON PATH

The result will be as shown below

[

{

"ItemID": 101,

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

{

"ItemID": 110,

"ItemDesc": "Printer Catridge",

"Unitprice": 112

{

"ItemID": 112,

"ItemDesc": "Copier Ink",

"Unitprice": 20

{

"ItemID": 123,

"ItemDesc": "Wireless Mouse",

"Unitprice": 30

}

]

Adding a ROOT key

If you want to associate a root key name to it then you need to tweak the query as below

SELECT *

FROM @Items

FOR JSON PATH,ROOT('Items')

And then output will become as below

{

"Items": [

{

"ItemID": 101,

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

{

"ItemID": 110,

"ItemDesc": "Printer Catridge",

"Unitprice": 112

{

"ItemID": 112,

"ItemDesc": "Copier Ink",

"Unitprice": 20

{

"ItemID": 123,

"ItemDesc": "Wireless Mouse",

"Unitprice": 30

}

]

}

If you analyze the above output you can see that it returns an array of JSON objects within [] with key value as Items.

Now in case we are getting a single item from the query and want to return it as a single JSON document without the array wrapper we can use the optional clause WITHOUT_ARRAY_WRAPPER

ie like this

SELECT TOP 1 *

FROM @Items

FOR JSON PATH,WITHOUT_ARRAY_WRAPPER

And we will get output as below

{

"ItemID": 101,

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

}

Without the optional clause the output will be a single JSON object array as below

[{

"ItemID": 101,

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

}]

Handling of NULL values

Now lets see how NULL values are handled by FOR JSON construct

Lets add two more rows to the table as per below script and check

declare @Items table

(

ItemID int,

ItemDesc varchar(100),

Unitprice decimal(10,2)

)

insert @Items

values(101,'Monitor CRT 18"',200),

(110,'Printer Catridge',112),

(112,'Copier Ink',20),

(123,'Wireless Mouse',30),

(123,NULL,70),

(123,'LED Monitor',NULL)

SELECT *

FROM @Items

FOR JSON PATH,ROOT('Items')

And by default output will be below

{

"Items": [

{

"ItemID": 101,

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

{

"ItemID": 110,

"ItemDesc": "Printer Catridge",

"Unitprice": 112

{

"ItemID": 112,

"ItemDesc": "Copier Ink",

"Unitprice": 20

{

"ItemID": 123,

"ItemDesc": "Wireless Mouse",

"Unitprice": 30

{

"ItemID": 123,

"Unitprice": 70

{

"ItemID": 123,

"ItemDesc": "LED Monitor"

}

]

}

As you see from above be default it ignores the elements having NULL values in each JSON document.

If you want to include NULL values also in the resultset you need to specify optional parameter INCLUDE_NULL_VALUES

SELECT *

FROM @Items

FOR JSON PATH,ROOT('Items'),INCLUDE_NULL_VALUES

And the output will modify as below

{

"Items": [

{

"ItemID": 101,

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

{

"ItemID": 110,

"ItemDesc": "Printer Catridge",

"Unitprice": 112

{

"ItemID": 112,

"ItemDesc": "Copier Ink",

"Unitprice": 20

{

"ItemID": 123,

"ItemDesc": "Wireless Mouse",

"Unitprice": 30

{

"ItemID": 123,

"ItemDesc": null,

"Unitprice": 70

{

"ItemID": 123,

"ItemDesc": "LED Monitor",

"Unitprice": null

}

]

}

Which will also include the elements with NULL values as you see from above

Generating Nested JSON documents

Now lets consider the full set of tables as given below

declare @order table

(

OrderID int,

OrderDesc varchar(1000),

OrderDate datetime,

CustomerID int,

ShippedDate datetime,

WebOrder bit

)

declare @OrderItems table

(

OrderItemID int,

OrderID int,

ItemID int,

Qty int

)

declare @Items table

(

ItemID int,

ItemDesc varchar(100),

Unitprice decimal(10,2)

)

declare @Customers table

(

CustomerID int,

CustomerDesc varchar(100)

)

declare @SubItems table

(

SubItemID int,

ItemID int,

SubItemDesc varchar(100)

)

insert @Order

values (100001,'Order 1','20130212',1004,'20130220',0),

(100002,'Order 2','20150624',1002,'20150630',0),

(100005,'Order 5','20160517',1012,'20160522',1)

insert @OrderItems

values (10005,100001,123,10),

(10007,100001,112,8),

(10009,100002,110,12),

(10011,100005,101,28)

insert @Items

values(101,'Monitor CRT 18"',200),

(110,'Printer Catridge',112),

(112,'Copier Ink',20),

(123,'Wireless Mouse',30)

insert @SubItems

values (118,110,'SP1'),

(123,110,'SP3'),

(130,112,'SP5'),

(140,112,'SP12'),

(144,123,'SP17')

insert @Customers

values (1002,'ABC inc'),

(1004,'KBC Associates'),

(1012,'AAM & Co')

There are two ways to generate nested JSON structure from the above data

The straightforward way is to specify the nesting level as an alias in the FOR JSON query.

The query will look like this

SELECT o.OrderDesc,o.OrderDate,o.ShippedDate,o.WebOrder,

oi.Qty AS 'OrderItem.OrderQty',

i.ItemDesc AS 'OrderItem.Item.ItemDesc',

i.Unitprice AS 'OrderItem.Item.UnitPrice',

s.SubItemDesc AS 'OrderItem.Item.SubItem.SubItemDesc',

c.CustomerDesc AS 'Customer.CustomerDesc'

FROM @order o

INNER JOIN @OrderItems oi

ON oi.OrderID = o.OrderID

INNER JOIN @Items i

ON i.ItemID = oi.ItemID

INNER JOIN @Customers c

ON c.CustomerID = o.CustomerID

INNER JOIN @SubItems s

ON s.ItemID = i.ItemID

FOR JSON PATH,ROOT('Orders')

And the resulting JSON will be as below

{

"Orders": [

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItem": {

"OrderQty": 10,

"Item": {

"ItemDesc": "Wireless Mouse",

"UnitPrice": 30,

"SubItem": {

"SubItemDesc": "SP17"

}

"Customer": {

"CustomerDesc": "KBC Associates"

}

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItem": {

"OrderQty": 8,

"Item": {

"ItemDesc": "Copier Ink",

"UnitPrice": 20,

"SubItem": {

"SubItemDesc": "SP5"

}

"Customer": {

"CustomerDesc": "KBC Associates"

}

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItem": {

"OrderQty": 8,

"Item": {

"ItemDesc": "Copier Ink",

"UnitPrice": 20,

"SubItem": {

"SubItemDesc": "SP12"

}

"Customer": {

"CustomerDesc": "KBC Associates"

}

{

"OrderDesc": "Order 2",

"OrderDate": "2015-06-24T00:00:00",

"ShippedDate": "2015-06-30T00:00:00",

"WebOrder": false,

"OrderItem": {

"OrderQty": 12,

"Item": {

"ItemDesc": "Printer Catridge",

"UnitPrice": 112,

"SubItem": {

"SubItemDesc": "SP1"

}

"Customer": {

"CustomerDesc": "ABC inc"

}

{

"OrderDesc": "Order 2",

"OrderDate": "2015-06-24T00:00:00",

"ShippedDate": "2015-06-30T00:00:00",

"WebOrder": false,

"OrderItem": {

"OrderQty": 12,

"Item": {

"ItemDesc": "Printer Catridge",

"UnitPrice": 112,

"SubItem": {

"SubItemDesc": "SP3"

}

"Customer": {

"CustomerDesc": "ABC inc"

}

]

}

As you see from above result the JSON document is generated in nested order with each level coming under corresponding parent level as specified by the alias names used.

But one thing to note here is that the entire parent hierarchy gets repeated for each instance of the child document. If you want to avoid this then you need to return the child elements as an array within parent element.

Inorder to get it in array format the query will look like this

SELECT o.OrderDesc,o.OrderDate,o.ShippedDate,o.WebOrder,

(SELECT Qty AS OrderQty,

(SELECT ItemDesc,Unitprice,

(SELECT SubItemDesc

FROM @SubItems

WHERE ItemID = i.ItemID

FOR JSON PATH) AS SUbItem

FROM @Items i

WHERE ItemID = oi.ItemID

FOR JSON PATH) AS Item

FROM @OrderItems oi

WHERE oi.OrderID = o.OrderID

FOR JSON PATH) AS OrderItems,

(SELECT CustomerDesc

FROM @Customers

WHERE CustomerID = o.CustomerID

FOR JSON PATH)AS Customer

FROM @order o

FOR JSON PATH,ROOT('Orders')

As you see the query itself will include nested subqueries in that case

Check the result and you will see child documents coming in array format without parent getting repeated as below

{

"Orders": [

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItems": [

{

"OrderQty": 10,

"Item": [

{

"ItemDesc": "Wireless Mouse",

"Unitprice": 30,

"SUbItem": [

{

"SubItemDesc": "SP17"

}

]

}

]

{

"OrderQty": 8,

"Item": [

{

"ItemDesc": "Copier Ink",

"Unitprice": 20,

"SUbItem": [

{

"SubItemDesc": "SP5"

{

"SubItemDesc": "SP12"

}

]

}

]

}

"Customer": [

{

"CustomerDesc": "KBC Associates"

}

]

{

"OrderDesc": "Order 2",

"OrderDate": "2015-06-24T00:00:00",

"ShippedDate": "2015-06-30T00:00:00",

"WebOrder": false,

"OrderItems": [

{

"OrderQty": 12,

"Item": [

{

"ItemDesc": "Printer Catridge",

"Unitprice": 112,

"SUbItem": [

{

"SubItemDesc": "SP1"

{

"SubItemDesc": "SP3"

}

]

}

]

}

"Customer": [

{

"CustomerDesc": "ABC inc"

}

]

{

"OrderDesc": "Order 5",

"OrderDate": "2016-05-17T00:00:00",

"ShippedDate": "2016-05-22T00:00:00",

"WebOrder": true,

"OrderItems": [

{

"OrderQty": 28,

"Item": [

{

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

}

]

}

"Customer": [

{

"CustomerDesc": "AAM & Co"

}

]

}

]

}

2. FOR JSON AUTO

As the name implies AUTO mode of the FOR JSON construct generates the JSON documents automatically based on the hierarchy we specify in the query

To see this in action lets use the same query we used above in AUTO mode

The query will look like this

SELECT o.OrderDesc,o.OrderDate,o.ShippedDate,o.WebOrder,

oi.Qty,

i.ItemDesc,

i.Unitprice,

s.SubItemDesc,

c.CustomerDesc

FROM @order o

INNER JOIN @OrderItems oi

ON oi.OrderID = o.OrderID

INNER JOIN @Items i

ON i.ItemID = oi.ItemID

INNER JOIN @Customers c

ON c.CustomerID = o.CustomerID

INNER JOIN @SubItems s

ON s.ItemID = i.ItemID

FOR JSON AUTO,ROOT('Orders')

The resulting JSON would be as below

{

"Orders": [

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"oi": [

{

"Qty": 10,

"i": [

{

"ItemDesc": "Wireless Mouse",

"Unitprice": 30,

"s": [

{

"SubItemDesc": "SP17",

"c": [

{

"CustomerDesc": "KBC Associates"

}

]

}

]

}

]

{

"Qty": 8,

"i": [

{

"ItemDesc": "Copier Ink",

"Unitprice": 20,

"s": [

{

"SubItemDesc": "SP5",

"c": [

{

"CustomerDesc": "KBC Associates"

}

]

{

"SubItemDesc": "SP12",

"c": [

{

"CustomerDesc": "KBC Associates"

}

]

}

]

}

]

}

]

{

"OrderDesc": "Order 2",

"OrderDate": "2015-06-24T00:00:00",

"ShippedDate": "2015-06-30T00:00:00",

"WebOrder": false,

"oi": [

{

"Qty": 12,

"i": [

{

"ItemDesc": "Printer Catridge",

"Unitprice": 112,

"s": [

{

"SubItemDesc": "SP1",

"c": [

{

"CustomerDesc": "ABC inc"

}

]

{

"SubItemDesc": "SP3",

"c": [

{

"CustomerDesc": "ABC inc"

}

]

}

]

}

]

}

]

}

]

}

As you see from the above the JSON structure is automatically generated based on the query sequence. We dont have much control over JSON structure generated.

Now lets see how AUTO mode behaves for the second query

{

"Orders": [

{

"OrderDesc": "Order 1",

"OrderDate": "2013-02-12T00:00:00",

"ShippedDate": "2013-02-20T00:00:00",

"WebOrder": false,

"OrderItems": [

{

"OrderQty": 10,

"Item": [

{

"ItemDesc": "Wireless Mouse",

"Unitprice": 30,

"SUbItem": [

{

"SubItemDesc": "SP17"

}

]

}

]

{

"OrderQty": 8,

"Item": [

{

"ItemDesc": "Copier Ink",

"Unitprice": 20,

"SUbItem": [

{

"SubItemDesc": "SP5"

{

"SubItemDesc": "SP12"

}

]

}

]

}

"Customer": [

{

"CustomerDesc": "KBC Associates"

}

]

{

"OrderDesc": "Order 2",

"OrderDate": "2015-06-24T00:00:00",

"ShippedDate": "2015-06-30T00:00:00",

"WebOrder": false,

"OrderItems": [

{

"OrderQty": 12,

"Item": [

{

"ItemDesc": "Printer Catridge",

"Unitprice": 112,

"SUbItem": [

{

"SubItemDesc": "SP1"

{

"SubItemDesc": "SP3"

}

]

}

]

}

"Customer": [

{

"CustomerDesc": "ABC inc"

}

]

{

"OrderDesc": "Order 5",

"OrderDate": "2016-05-17T00:00:00",

"ShippedDate": "2016-05-22T00:00:00",

"WebOrder": true,

"OrderItems": [

{

"OrderQty": 28,

"Item": [

{

"ItemDesc": "Monitor CRT 18\"",

"Unitprice": 200

}

]

}

"Customer": [

{

"CustomerDesc": "AAM & Co"

}

]

}

]

}

Conclusion

As you see from all the above illustrations FOR JSON provides a convenient way of generating JSON documents from relational table data.
Based on flexibility you need, you can use PATH or the AUTO mode along with FOR JSON for generating the JSON documents in the required format.
See you all with the next article in the series which will explain on how to extract data from JSON documents to relational tables. Till then happy coding with the sample scripts above. Feel free to revert for any clarifications.

Whats New in SSIS 2016 - Expose SSIS Package Data Externally as a View

noreply@blogger.com (Visakh Murukesan) — Fri, 17 Jun 2016 16:04:00 +0000

Introduction

SQL 2016 has brought in lots of new and exciting features on the plate. One of the pretty cool feature that was added to SSIS in SQL 2016 is the ability to expose the data directly as a SQLServer database view object which can be accessed using T-SQL queries.

This is made possible by the introduction of two new components

1. A new linked server provider SSISOLEDB which is capable of exposing the SSIS data to SQLServer database

2. A new data flow component called Data Streaming Destination which will stream the data to be consumed through the linked server based on the above provider

Once you've installed SQL 2016 you can connect to the instance using management studio and when you expand the Server Objects - Linked Servers - Providers you can see this new provider listed as per below

Similarly you can launch SSDT (SQLServer Data Tools) 2015 and start an Integration Services project and create a sample package with a single data flow task. Inside data flow task tab you can see the new destination component available

Now that we have seen the two components lets see how they can be used together for exposing SSIS data as a SQLServer database object.

Illustration

Consider the scenario where we have data coming from different sources which needs to be exposed to our database as an object.
For the sake of this article lets consider the below scenario
1. Customer details coming from a SQL database
2. Customet Satisfaction score data coming from an Excel spreadsheet

The requirement is to merge the data from the above sources to be accessed within third database most often a datawarehouse for analytical and reporting needs.
For this kind of requirement the obvious way to approach is to create a SSIS package which can extract the required data from the various source and do the merge operation based on the related columns.
Such a package for the above case would look like this

The data flow tasks will look like below

The sources involved here are SQL database which has the Customer details as per below

CREATE TABLE OrderDetails

(

OrderID int IDENTITY(1,1),

CustomerName varchar(100),

CustomerEMail varchar(100),

OrderDesc varchar(100),

OrderPrice Decimal(10,2),

OrderDate datetime

)

INSERT OrderDetails (CustomerName,CustomerEMail,OrderDesc,OrderPrice,OrderDate)

VALUES ('Customer 1','abc@xyz.com','Order ABC',130,'20151104'),

('Customer 2','kytrtetet@treby.com','Order PQR',2150.50,'20151203'),

('Customer 5','887rtert767@hakii.co.hk','Order for DEF and Co',1785,'20160112'),

('Customer 7','hjgjh@kaz.co.in','M/S Bhavya Comp Ltd',890,'20160120'),

('Customer 9','ewrewrr@oiuiiuo.co.in','XYZ',1125,'20160205'),

('Customer 1','abc@xyz.com','DEF',3400,'20160322'),

('Customer 9','ewrewrr@oiuiiuo.co.in','MNU',215,'20160312'),

('Customer 14','ytrytr@terry.co.in','PUK',345,'20160415'),

('Kirby Stevens','kirby.stevens@jackoss.co.eu','WKP',2300,'20160420'),

('Kerney Michelle','kerney@hkrky.co.ae','VER',485,'20160512'),

('Customer 1','abc@xyz.com','RTU',1320,'20160514'),

('Mark Bowerman','mark.2134@kayko.com','BJY',1130,'20160603'),

('Customer 9','ewrewrr@oiuiiuo.co.in','SAR',765,'20160610')

And the query used is as below to get the count and total value of orders per customer

SELECT CustomerName,

CustomerEMail,

COUNT(OrderDesc) AS OrderCount,

SUM(OrderPrice) AS TotalOrderPrice

FROM OrderDetails

GROUP BY CustomerName,

CustomerEMail

ORDER BY CustomerEMail

Source 2 is an excel spreadsheet with customer satisfaction data as below

And for this illustration we use the above query to get the average customer satisfaction score and response count

SELECT email,

AVG(satscore) AS AvgSatScore,

COUNT(*) AS responseCount

FROM `Sheet1$`

GROUP BY email

ORDER BY email

From these two sources of data would be merged based on the common columns between the two resultsets as shown below

Once this is done we would get the required resultset. Now inorder to stream the resultset we will use Data Streaming Destination component and include the required columns to be accessed through the exposed view.

Data Streaming Destination will include the required columns as shown above

Once this is done we need to deploy the package to the Integration Services Catalog.

The package will get deployed to the Integration Services Server as shown below

Once this is done we need to stream the package data as a view within a database in the associated instance.

This can be done using data feed publishing wizard which comes with the SQL 2016 installation.

We can launch data feed publishing wizard and it will come up with the below window

Click Next and it will ask you to select the server and package to be published

Once selected it will also give you the option to modify the default values set for the connection managers and parameters in the package which I'm not changing for this example.

Clicking Next will bring it to Publish settings screen where we will specify the view name through which we need to expose the package data.

One thing to note here is that Use 32Bit Runtime is set to True as one of the sources involved here is an Excel spreadsheet and the ACE driver installed is of 32 bit version in the server. In case you've the 64 bit version installed then you don't need to tweak this setting.

On clicking Next it goes to the validation screen and validates the various steps involved

Click on Next and finally click Finish to publish the package as a view

Once publishing is done you can connect to the server instance through SQL Management Studio and go to the specified database and expand views to see the view you published

Right click and Select data from the view and it will execute the package on the background and will provide you with the below result

You can confirm this by expanding Integration Services Catalog and finding the package within your deployed project and then right clicking and choosing the All Executions report which will show the log for successful execution of the package when view gets referred in the query

Conclusion

As shown by the illustration above you can use the new data feed publishing wizard available in SQL 2016 for exposing a SSIS package resultset as a view object within an associated relational database. This is a very useful feature which can be used for publishing a resultset consisting of data extracted from a variety of sources.