SQL Server 2016 offers built-in support for JSON, in a similar way to the support offered for XML since SQL Server 2000. Both XML and JSON are semi-structured data formats as opposed to the structured relational data format. SQL Server is an RDBMS, which stands for Relational Database Management System. It has a relational engine. So why XML and JSON?
Why would I want to use JSON or XML in my relational database?
Well, there are several use cases for which it makes sense. Let’s explore them…
- Store Semi-Structured Data
You already have a relational database with structured data, but some of the data in your application is semi-structured, and doesn’t map well to a relational structure.
One of our clients is running an online casino with multiple games, such as Blackjack, Roulette and Slot Machine. One of the requirements is to store a complete structure of each game in order to replay it later on the website. Modeling this data in a relational structure requires a new set of tables and relationships for each game type. It is much easier to store this data as either JSON or XML in a single column in the “GameResults” table. In each row of this table there will be a JSON/XML document with the relevant information corresponding to the game type and the specific game details represented by that row. For example, a document describing Blackjack will contain the set of cards of the dealer and the player, and whether the player used insurance or not. But for the Roulette the information within the document will be completely different, with the winning number and all the bets on the table.
By leveraging XML/JSON support in SQL Server, it is much easier to develop and maintain the database with this single column. We can now query the column using built-in methods and functions or simply read entire documents to be pared by the application.
- Facilitate Design and Development
Some data structures dynamically change all the time. Trying to constantly modify a relational schema can result in quite a lot of headache, not to mention downtime.
One of our clients develops a CRM application on top of SQL Server. There is a reporting module in the application, which allows users to customize and save their reports. Behind the scenes, the database needs to store the structure of each saved report as well as its data. Since users create new reports and modify existing reports all the time, storing all the data in a relational format is quite a challenge. It requires handling DDL statements, constraints, indexes and large data updates. Instead, we decided to store the structure of the reports as well as their data in XML format. By doing this, all the modifications are now performed by the application, and they are completely transparent to the database. This approach dramatically reduces design, development and deployment effort.
- Integration with NoSQL Platforms
You might have an architecture that involves several data platforms. Some of the data, such as financial transactions, is stored in a relational database, while other data, such as web logs, is stored in a NoSQL database, such as MongoDB.
Most NoSQL platforms use JSON as the native data format. When data from the relational database and data from the NoSQL database need to be correlated and manipulated, you need a way to transform one data format to the other. This is where JSON support in SQL Server becomes really handy. You can convert relational data to JSON by using the FOR JSON clause in a SELECT statement, and you can also convert JSON objects to relational format by using the OPENJSON function.
- Integration with Web Services and Applications
Similar to the integration with NoSQL platforms, many architectures involve web services and web applications, either internal or external, where the native protocol is based on XML. In such cases, there are occasions in which you need to extract relational data from an XML document and insert it into the relational database, or vice versa. Just like with JSON, you can use FOR XML and OPENXML to achieve these goals.
- T-SQL Tricks
This is not the main reason for using XML ort JSON in a relational database, but it can be handy in some cases. There are all kinds of creative ways to leverage XML/JSON functionality in order to achieve relational functionality. One such example is the use of FOR XML to perform a type of unpivot on a data set. I used this technique in my redundant indexes script, which identifies indexes that can be potentially dropped without a risk.
- Leverage Existing Skillset and Tooling
In today’s fast moving world, it is no longer appropriate to choose a single data platform for all of the data in your application. You should choose the right data platform for each data type depending on the structure, the usage, the volume, and more.
This means that you need to be familiar with many different types of data platforms, and then you need to install and manage these platforms. While this is the right choice from a design point of view, it requires a lot of training and management efforts.
Sometimes it makes more sense to leverage your existing skills and tools, even if it’s not the perfect solution, and even if it means you will pay a price in terms of performance or scalability. The price you will pay for training and management might be higher. So it might make sense to use SQL Server to store and manage JSON data, even when the right solution would be to use MongoDB.
These situations are tough. You need to look far enough into the future and estimate the growth and the cost of using a relational database vs. the cost of using another platform. In some cases, the right choice for you is to use SQL Server, and then the support we have for JSON and XML can become very handy.
XML is even more powerful than JSON, because SQL Server offers a native data type with powerful methods, XML schema collections, XML indexes and more.
So, as you can see, there are some use cases for using XML or JSON in a relational database. Of course, there are a lot of cases where people use XML/JSON in a relational database for the wrong reasons, where they shouldn’t. As part of the system architecture and the database design, you need to understand the nature of the data (structure, volume, velocity, importance, etc.), evaluate the costs and choose the best data platform for each case.
If you choose to use XML or JSON in SQL Server, then learn how to leverage the support offered by SQL Server for these two data formats.
If you want to learn more, then join me for my online session at 24 Hours of PASS on September 7th at 16:00 GMT. The session title is From XML to JSON. In this session I will cover in more details the two data formats and how they are supported in SQL Server, including a lot of demo. And if you are attending PASS Summit 2016 in October, then I will be doing a longer and more detailed version of this session there too.
The post Why Would I Want to Use JSON in SQL Server? appeared first on Madeira Data Solutions.
