In Part 1 of this series, we discussed what is JSON and how we can generate data in a table in JSON format and how to parse JSON data using SQL. In this post, we will go through when we may need to store JSON data in SQL server.
When do you think it is appropriate to store JSON in SQL Server?
SQL Server 2016, makes it possible for some “non-SQL” data storage to be stored as a traditionally tabled-based SQL Server. So, does this mean all data in SQL Server going forward should be stored in long JSON strings? No, that would be a terrible idea.
There are instances when storing JSON in SQL Server is a good choice, though. In this post, I want to create recommendations for when data should be stored as JSON and when it shouldn’t.
Here we have an application database (“InventoryApp”) that consists of only a single table (“Sales.Data”) with three JSON NVARCHAR(50) columns to represent all of the data required by the app. Relationships exist between Sales, Purchases, and Customers but these are not defined on the database side. In a relational world of SQL server this is not a very good design approach, but nowadays many applications are building their services around abstracting the database layer away from developers, essentially storing entire tables or databases in large JSON strings. Storing data in this manner is preferred by most mobile app developers as it is easy to deserialize JSON string into objects in their programming language, they are using in their app. They like the fact that with JSON they can have an infinitely changing storage schema (just add new keys, values, and arrays!) so if they need a new field for their app, they can just add it in, serialize the object to a JSON string, and store it again in the database.
Obviously, going completely “NoSQL” might make short-term development easier/quicker, but using SQL Server 2016 to only store data this way is a joke: as we would not be able to use SQL Server’s amazing performance, schema definition and validation, and security features.
Use Case #1: Error Logging
In most of the programming languages, it is easy to convert error messages and run-time values to JSON object on error. Since error messages and data values change in structure depending on where they occur, it’s easy to dynamically turn any type of object into JSON data.
This data is perfect to store in SQL to be looked at later. Most of the time developers use nvarchar(max) for storing error information. With SQL Server 2016, it is now easier to examine and parse the error information directly in SQL Server Management Studio with the variety of JSON parsing functions available.
Use Case #2: Piloting Ideas
Most workplaces restrict developers from making any change in production, due to various reasons. If they want to test any changes then they have to go through long approval processes, here JSON can be useful for them. A nvarchar(max) in a table can have its JSON data be easily added to and modified to fit more data than it was originally intended to hold. All without any database structure change requests.
This is not an ideal situation and requires good planning from application side before implementing.
However, if a “flexible” JSON column is built with eventual conversion to a traditional table structure in mind from the start, it’s actually simple for a developer to transition an entirely JSON storage structure to a relational format later on. The key here is that the developer needs to have this conversion planned from day one.
Use Case #3: Non-Analytical Data
Sometimes not all data needs to be analyzed. Often an app might need to save some session data to a database temporarily — why bother creating all of the maintenance overhead of strict database schemas if the data will never be queried for analytical purposes? An example can be a website’s dynamically created user profile settings. You can build normalized table(s) to store all of that data, but then you will be writing programming logic to normalize and denormalize your data out of the app.If this data will not have to be searched, then why bother adding all of the overhead? Keep it in JSON and be done with.
There can be more use cases, I would appreciate if you can provide your use case in comments where you need to store JSON data in SQL server and parse it.
Comments
Post a Comment