Is it just me or is British Summer Time (BST) AKA Daylight Saving Time (DST) an utterly pointless endeavour these days? Admittedly, on its introduction in 1916, it fulfilled a sensible objective – to provide more daylight hours during the summer. For agricultural, construction or other services that are reliant on sufficient light to carry out their work, this was a godsend. In today’s modern world, with the proliferation of electricity and lighting services in almost every corner of the UK, the whole concept now appears to be a curious relic of the western world. No major Asian, African, South American country adopts the practice and, given the increased importance that these continents now play on the global stage, it wouldn’t surprise me if the whole concept becomes consigned to the scrapheap within our lifetimes.

My fury concerning BST surfaces thanks to my experience working with IT systems and, in particular, Microsoft Azure. Typically, any service that has a Windows OS backend involved will do a pretty good job in determining the correct locale settings that need applying, including BST/DST. These settings will generally inherit into most applications installed on the machine, including SQL Server. You can verify this at any time by running the following query, kindly demonstrated by SQL Server legend Pinal Dave, on your SQL Server instance:

As you can see from the underlying query, it is explicitly checking a Registry Key Value on the Windows Server where SQL Server resides – which has been set up for British (UK) locale settings. The Registry Key folder will, additionally, include information to tell the machine when to factor in BST/DST.

This is all well and good if we are managing dedicated, on-premise instances of SQL Server, where we have full control over our server environments. But what happens on a Single Azure SQL database within the UK South region? The above code snippet is not compatible with Azure SQL, so we have no way of finding out ourselves. We must turn to the following blog post from Microsoft to clarify things for us:

Currently, the default time zone on Azure SQL DB is UTC. Unfortunately, there is not possible to change by server configuration or database configuration.

We can verify this by running a quick GETDATE() query and comparing it against the current time during BST:

(@@VERSION returns the current edition/version of the SQL instance which, in this case, we can confirm is Azure SQL)

The result of all of this is that all date/time values in Azure SQL will be stored in UTC format, meaning that you will have to manage any conversions yourself between interfacing applications or remote instances. Fortunately, there is a way that you can resolve this issue without ever leaving Azure SQL.

On all versions of SQL Server, Microsoft provides a system view that returns all time zones that are supported for the instance. Using this query, we can determine the correct timezone instance to use for BST by filtering for all time zones:

SELECT *
FROM sys.time_zone_info
ORDER BY [name]

As highlighted above, for BST, GMT Standard Time is our timezone of choice, and we can see the correct offset. An additional flag field is included to indicate whether it is currently applicable or not.

With the name value in hand, we have everything we need to start working with a query hint that I was overjoyed to recently discover – AT TIME ZONE. When included as part of selecting a date field type (datetime, datetime2 etc.), it adds the time-offset value to the end of the date value. So, with some tinkering to our earlier GETDATE() query, we get the following output:

SELECT GETDATE() AT TIME ZONE 'GMT Standard Time', @@VERSION

While this is useful, in most cases, we would want any offset to be automatically applied against our Date/Time value. With some further refinement to the query via the DATEADD function, this requirement becomes achievable, and we can also view each value separately to verify everything is working as intended:

SELECT GETDATE() AS CurrentUTCDateTime,
	   GETDATE() AT TIME ZONE 'GMT Standard Time' AS CurrentUTCDateTimeWithGMTOffset,
	   DATEADD(MINUTE, DATEPART(tz, GETDATE() AT TIME ZONE 'GMT Standard Time'), GETDATE()) AS CurrentGMTDateTime,
	   @@VERSION AS SQLVersion
	   
	   

Even better, the above works regardless of whether the offset is an increase or decrease to UTC. We can verify this fact by adjusting the query to instead convert into Haiti Standard Time which, at the time of writing, is currently observing DST and has a 4 hour UTC offset:

So as we can see, a few lines of codes means that we can straightforwardly work with data in our desired locale. 🙂

It does seem somewhat counter-intuitive that a service, such as Azure SQL, hosted within a specific location, does not work in the correct date/time formats for that region. When you consider the global scale of the Azure network and the challenges this brings to the fore, the decision to revert to a single time zone for all systems does make sense and provides a level & consistent playing field for developers. One comment I would have is that this particular quirk does not appear to be well signposted for those who are just getting started with the service, an omission that could cause severe business or technical problems in the long term if not correctly detected. What is ultimately most fortuitous is the simple fact that no overly complex coding or client application changes are required to fix this quirk. Which is how all IT matters should be – easy and straightforward to resolve.

When it comes to handling large datasets in a formal, structured and highly scalable manner, nothing beats SQL Server. Having worked with the product for almost six years, I always look forward to the opportunity of putting together some SQL queries or to build out a new database. I think of it as a nice little treat, a reward amongst the midst of other, sometimes tedious, tasks that I have to deal with on a weekly basis. I rank knowledge of SQL Server pretty highly if you are aiming to become a serious Dynamics CRM/365 for Customer Engagement professional and I credit my experience with SQL as one of the things that helped to streamline my journey into Dynamics.

It may be, however, that others are not as keen at the prospect of working with SQL databases, particularly when it is unable to accommodate some of the alternative data storage mechanisms that are commonplace with application developers. A good example of this is JSON (JavaScript Object Notation), a format that is used widely today as a portable and “easy to read” mechanism of transferring data. For someone who is more used to working with SQL, getting your head around JSON can be a bit of a challenge at first and – traditionally – was not something that Microsoft factored into the design of SQL Server. A lot has changed with Microsoft – to the extent that services such as Microsoft Flow and Azure now use JSON extensively – and with SQL Server, as a whole host of related functions were added to SQL Server 2014 to provide straightforward conversions into JSON. The FOR JSON PATH clause is the primary gateway into this world and is a function which I have slowly, but surely, started to get my head around. What I wanted to do in this week’s blog post was provide a “crash course” on how to use this nifty piece of functionality, hopefully with the aim of giving you everything you need to start utilising it in your environment(s).

Before we begin…

To best illustrate how the clause works in practice, it is necessary to create an appropriate set of inter-related tables within SQL Server, that will be used for all examples that follow. Here’s one I (rather unimaginatively) made earlier:

The code snippets to create them can be found below:

CREATE TABLE dbo.[ParentTable]
(
	ParentTableID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	Field1	BIT		NOT NULL,
	Field2	CHAR(10)	NULL,
	Field3	VARCHAR(MAX) NULL
)

GO

CREATE TABLE dbo.[ChildTable1]
(
	ChildTable1ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	ParentTableID INT FOREIGN KEY REFERENCES dbo.[ParentTable](ParentTableID) NULL,
	Field1	BIT		NOT NULL,
	Field2	CHAR(10)	NULL,
	Field3	VARCHAR(MAX) NULL
)

CREATE TABLE dbo.[ChildTable2]
(
	ChildTable2ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	ParentTableID INT FOREIGN KEY REFERENCES dbo.[ParentTable](ParentTableID) NULL,
	Field1	BIT		NOT NULL,
	Field2	CHAR(10)	NULL,
	Field3	VARCHAR(MAX) NULL
)

GO

CREATE TABLE dbo.[GrandchildTable]
(
	GrandchildTableID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	ChildTable2ID INT FOREIGN KEY REFERENCES dbo.[ChildTable2](ChildTable2ID) NULL,
	Field1	BIT		NOT NULL,
	Field2	CHAR(10)	NULL,
	Field3	VARCHAR(MAX) NULL
)

GO

The table structures are incredibly basic, but note, in particular, the FOREIGN KEY relationships from the 2 Child Tables to the Parent and also the additional parent/child relationship between the GrandchildTable and ChildTable2. You will also need to look at inserting some test data into the tables to properly follow through the rest of this post.

With our environment prepped, let’s take a look at the different ways we can convert our dataset into JSON format, with minimal effort involved.

Example 1: FOR JSON AUTO

If we were to look at doing a straightforward SELECT * query on all our tables, our query and expected output might look something like this:

SELECT *
FROM dbo.ParentTable AS PT
 INNER JOIN dbo.ChildTable1 AS CT1
  ON PT.ParentTableID = CT1.ParentTableID
 INNER JOIN dbo.ChildTable2 AS CT2
  ON PT.ParentTableID = CT2.ParentTableID
 INNER JOIN dbo.GrandchildTable AS GT
  ON CT2.ChildTable2ID = GT.ChildTable2ID

Our main issue with this query is that, because of how T-SQL works, the 2 ParentTable records are returned for every child and grandchild record. For a client application, this can be somewhat cumbersome to handle. FOR JSON AUTO can be straightforwardly added to the end of the above query to convert the query output accordingly:

SELECT *
FROM dbo.ParentTable AS PT
 INNER JOIN dbo.ChildTable1 AS CT1
  ON PT.ParentTableID = CT1.ParentTableID
 INNER JOIN dbo.ChildTable2 AS CT2
  ON PT.ParentTableID = CT2.ParentTableID
 INNER JOIN dbo.GrandchildTable AS GT
  ON CT2.ChildTable2ID = GT.ChildTable2ID
FOR JSON AUTO

//Example output of the first 25 lines below:

[{
    "ParentTableID": 1,
    "Field1": true,
    "Field2": "Test      ",
    "Field3": "This is a test record",
    "CT1": [{
        "ChildTable1ID": 1,
        "ParentTableID": 1,
        "Field1": true,
        "Field2": "Test      ",
        "Field3": "This is a test record",
        "CT2": [{
            "ChildTable2ID": 1,
            "ParentTableID": 1,
            "Field1": false,
            "Field2": "Test      ",
            "Field3": "This is a test record",
            "GT": [{
                "GrandchildTableID": 1,
                "ChildTable2ID": 1,
                "Field1": false,
                "Field2": "Test      ",
                "Field3": "This is a test record"
            }]
       ...

This output provides a much more sensible structure, with no record duplication and proper nesting of child records.

Example 2: FOR JSON PATH, ROOT

With some modifications to the above query, it is also possible to specify names for each root element. This can be tailored depending on your specific requirements. For example, let’s say we had to provide the following root element names for each of the example tables:

  • dbo.ParentTable -> Parent
  • dbo.ChildTable1 -> FirstChildTable
  • dbo.ChildTable2 -> SecondChildTable

The following query would achieve these requirements, in addition to adding a master root element name of MyTestSQLJSONObject:

SELECT PT.ParentTableID AS [Parent.ParentTableID], PT.Field1 AS [Parent.Field1], PT.Field2 AS [Parent.Field2], PT.Field3 AS [Parent.Field3],
	   ChildTable1ID AS [FirstChildTable.ChildTable1ID], CT1.Field1 AS [FirstChildTable.Field1], CT1.Field2 AS [FirstChildTable.Field2], CT1.Field3 AS [FirstChildTable.Field3],
	   CT2.ChildTable2ID AS [SecondChildTable.ChildTable1ID], CT2.Field1 AS [SecondChildTable.Field1], CT2.Field2 AS [SecondChildTable.Field2], CT2.Field3 AS [SecondChildTable.Field3],
	   GT.GrandchildTableID AS [GrandchildTable.GrandchildTableID], GT.Field1 AS [GrandchildTable.Field1], CT2.Field2 AS [GrandchildTable.Field2], CT2.Field3 AS [GrandchildTable.Field3]
FROM dbo.ParentTable AS PT
 INNER JOIN dbo.ChildTable1 AS CT1
  ON PT.ParentTableID = CT1.ParentTableID
 INNER JOIN dbo.ChildTable2 AS CT2
  ON PT.ParentTableID = CT2.ParentTableID
 INNER JOIN dbo.GrandchildTable AS GT
  ON CT2.ChildTable2ID = GT.ChildTable2ID
FOR JSON PATH, ROOT('MyTestSQLJSONObject')

//Example of first 25 lines below:

{
    "MyTestSQLJSONObject": [{
        "Parent": {
            "ParentTableID": 1,
            "Field1": true,
            "Field2": "Test      ",
            "Field3": "This is a test record"
        },
        "FirstChildTable": {
            "ChildTable1ID": 1,
            "Field1": true,
            "Field2": "Test      ",
            "Field3": "This is a test record"
        },
        "SecondChildTable": {
            "ChildTable1ID": 1,
            "Field1": false,
            "Field2": "Test      ",
            "Field3": "This is a test record"
        },
        "GrandchildTable": {
            "GrandchildTableID": 1,
            "Field1": false,
            "Field2": "Test      ",
            "Field3": "This is a test record"
        ...

Example 3: NULL Field Values

One thing worth bearing in mind when working with the FOR JSON clause is how NULL field values behave. Take a look at the following example query output from dbo.ParentTable:

When attempting to query this single record using the FOR JSON AUTO clause, we get the following output:

//Example output below. Notice that no field name/value is returned for Field2 now

[{
    "ParentTableID": 1,
    "Field1": true,
    "Field3": "This is a test record"
}]

If you have a requirement always to return a value for every NULL field, then you can use the INCLUDE_NULL_VALUES option to get around this:

SELECT *
FROM dbo.ParentTable AS PT
WHERE PT.ParentTableID = 1
FOR JSON AUTO, INCLUDE_NULL_VALUES
//Notice now that Field2 returns as expected

[{
    "ParentTableID": 1,
    "Field1": true,
    "Field2": null,
    "Field3": "This is a test record"
}]

Even with this option specified, there may still be issues with outputting this field with a value of null. In these scenarios, on a T-SQL side, you would generally use the ISNULL function to replace NULL values with an empty string. Further, because the field type in this example is a CHAR(10) data type, there are ten characters of whitespace that need removing from the output string. The following query will fix both of these problems:

SELECT ParentTableID, Field1, LTRIM(ISNULL(Field2, '')) AS Field2, Field3
FROM dbo.ParentTable AS PT
WHERE PT.ParentTableID = 1
FOR JSON AUTO

Example 4: Using sub-queries to return child records as JSON objects

In most cases involving parent and child records, returning all the data as JSON may not be required. Instead, it may be necessary to return the fields from the parent record only, and all child records as a single JSON object field on the parent record. Using Subqueries, we can accommodate this scenario as follows

SELECT PT.ParentTableID, PT.Field1, PT.Field2, PT.Field3,
(
	SELECT *
	FROM dbo.ChildTable1 AS CT1
	WHERE CT1.ParentTableID = PT.ParentTableID
	FOR JSON AUTO
) AS ChildTable1,
(
	SELECT *
	FROM dbo.ChildTable2 AS CT2
	 INNER JOIN dbo.GrandchildTable AS GT
	  ON CT2.ChildTable2ID = GT.ChildTable2ID
	WHERE CT2.ParentTableID = PT.ParentTableID
	FOR JSON AUTO
) AS ChildTable2
FROM dbo.ParentTable AS PT

Example 5: Storing FOR JSON Query Output in Parameters

In most scenarios, you will generally provide functions or Stored Procedures for developers to interface with when connecting to the database. It is in this situation where the ability to store the output of any query – including those that use the FOR JSON clause – within a parameter becomes very useful. The following snippet will store the output of a FOR JSON query into a parameter called @JSON, which is then retrievable at any time via a SELECT query:

DECLARE @JSON NVARCHAR(MAX) = (SELECT * FROM dbo.ChildTable2 AS CT2 INNER JOIN dbo.GrandchildTable AS GT ON CT2.ChildTable2ID = GT.ChildTable2ID FOR JSON AUTO)

SELECT @JSON

Wrapping Up: A Few Things to Bear in Mind

  • The FOR JSON clause is not compatible with Common Table Expressions (CTE’s).
  • When storing the output of a FOR JSON query in a parameter, you have no native capability to query the inner contents of the JSON object via a SELECT…WHERE query.
  • Because there is no way of specifying the name of the single column that returns as part of a FOR JSON query, you cannot create a view that uses this clause.

I hope that this post has given you all the information and guidance needed to start working with FOR JSON clauses in your queries. The ability to straightforwardly convert SQL tabular data into a format that most developers would readily scream for is a convenient feature to have at our disposal and is indicative of how far the SQL Server product has moved forward in recent years. Hopefully, this feature will save you some hassle in the future and will mean that you can make friends with your developer colleagues 🙂

I’ve gone on record previously saying how highly I rate the Dynamics CRM/Dynamics 365 Customer Engagement (CRM/D365CE) community. Out of all the groups I have been a part of in the past, you couldn’t ask for a more diverse, highly passionate and – most importantly of all – helpful community. There are a lot of talented individuals out there which put a metric tonne of effort into providing the necessary tools, know-how and support to make our daily journey with CRM/D365CE that much easier to traverse.

An excellent case in point comes from the CRM DevOps extraordinaire himself, Ben Walker, who reached out me regarding my recent post on default SiteMap areas vanishing mysteriously. Now, when you are working with tools like XrmToolbox, day in, day out, the propensity towards generating facepalm moments for not noticing apparent things can increase exponentially over time. With this in mind, Ben has very kindly demonstrated a much more simplistic way of restoring missing SiteMap areas and, as he very rightly points out, the amount of hassle and time-saving the XrmToolbox can provide when you fully understand its capabilities. With this in mind, let’s revisit the scenario discussed in the previous post and go through the insanely better approach to solving this issue:

  1. Download and run XrmToolbox and select the SiteMap Editor app, logging into your CRM/D365CE instance when prompted:

After logging in, you should see a screen similar to the below:

  1. Click on the Load SiteMap button to load the SiteMap definition for the instance you are connected to. It should bear some resemblance to the below when loaded:

  1. Expand the Area (Settings) node. It should resemble the below (i.e. no Group for Process Center):

  1. Right click on the Area (Settings) node and select Add Default SiteMap Area button. Clicking this will launch the SiteMap Component Picker window, which lists all of the sitemap components included by default in the application. Scroll down, select the ProcessCenter option. Then, after ticking the Add child components too checkbox, press OK. The SiteMap Editor will then add on the entire group node for the ProcessCenter, including all child nodes:

  1. When you are ready, click on the Update SiteMap button and wait until the changes upload/publish into the application. You can then log onto CRM/D365CE to verify that the new area has appeared successfully.

I love this alternative solution for a number of reasons. There are fewer steps involved, there is no requirement to resort to messing around with the SiteMap XML files (which has its own set of potential pitfalls, if done incorrectly) and the solution very much looks and feels like a “factory reset”, without any risk of removing other custom SiteMap areas that you may have added for alternate requirements. A huge thanks to Ben for reaching out and sharing this nifty solution and for rightly demonstrating how fantastic the CRM/D365CE community is 🙂