Advanced Data Types
Advanced Data Types
JSON support in MS SQL Server
Read JSON data in MS SQL
- Starting from MS SQL server 2016 you can convert JSON data to tabular format. It is possible using the newly introduced OPENJSON rowset function. Let’s find how!
Format MS SQL data in JSON format
- Prior to MS SQL server 2016 manipulating data in JSON format was not supported and sometimes a challenging task, forcing us to implement third party solutions (e.g. CLR). In this article we will see how we can create JSON objects from MS SQL data.
Modify JSON data using JSON_MODIFY
- After discussing about how MS SQL Server can help us to format MS SQL data in JSON format and how to read JSON data in MS SQL Server, in this article we will see how we can modify JSON data in MS SQL Server using JSON_MODIFY function.
JSON functions, tips & tricks
- After discussing about how MS SQL Server can help us to format MS SQL data in JSON format and how to read JSON data in MS SQL Server, in this article we will quickly pass over some additional functions, tips and tricks and possibilities handling JSON data in MS SQL Server.
Hierarchical Data and HierarchyID
Hierarchical data in MS SQL
- The usual solution how to handle hierarchy in a database was to use two columns. The first column contains the ID of the hierarchical member (in much cases also the primary, unique ID), and the other, the ID of its parent hierarchical member. From SQL Server 2008 we can use the HierarchyID data type that is a data type written in .NET and exposed in SQL Server. HierarchyID offers us some performance benefits and simplified code. There are also available many functions that can be used for example to determine the ancestors and descendants of a hierarchical member, as well as determine other useful information, such as the level of a specific hierarchical member within the hierarchy.
HierarchyID data type Methods
- In my previous article that you can find here I introduced you to the Hierarchical data in MS SQL. HierarchyID data type is a cool feature if you need to store and handle hierarchical data in your database (e.g. organizational chart). Here we will see methods exposed against the HierarchyID data type.
HierarchyID data type Performance, tips & tricks
- Now when we know more about hierarchies in MS SQL Server and we overviewed existing methods for the HierarchyID data type it’s time to discuss about the benefits of using HierarchyID data type.