At the beginning of the month I lectured at the SQLSaturday # 481 event about the new support of JSON in SQL Server 2016. During the demonstration I showed queries that returns JSON text. SQL Server returns the JSON text formatted as compact text / dense format (one line, no extra space), which is good for most cases that we want to store the text or pass it between machines for example. But, by using a compact format the structure of the JSON document is not readable.
In NoSQL servers, which based on JSON, we usually have a built-in method to format the JSON document into a format that is easier for humans to read (for example in mongoDB it named "pretty"). This feature must be added to the SSMS or to the SQL Server, in my opinion. In the mean time, for the sake of the lecture, I created simple app that do the job. it is basically a "one line app" using the nuget package Json.NET (Newtonsoft.Json).
After I uploaded the lecture materials (code and presentation), I got requests to upload the application as well. Since I do not like the idea that people execute code without understanding it, and since this is very simple and short code, and most DBA does not develop dot.net application, therefore I decided to post the code, as a step-by-step tutorial for beginners.
Step 1: create new project using the console application template
Step 2: Add references to Json.NET nuget package
>> Open "Solution Explorer" -> Right click on "References" -> click on "Add Reference.."
>> In the search box type "Json.NET", and chose the newer package version -> Click OK
Step 3: Replace the built in code with this code:
String EnterNewJSON =
"Enter JSON in one line, or empty string to end"
"Ronen Ariely Pretty JSON formatter"
String t =
t = ReadLongLine();
(t.Length > 1)
t = ReadLongLine();
 inputBuffer =
Stream inputStream = Console.OpenStandardInput(inputBuffer.Length);
You can now execute the application and start working with it, but I recommend to keep on reading.
Step 4: Configure the Console display
If you are using console application and you do not want to type the JSON each time, then you need to enable "QuickEdit Mode". Moreover, If we are going to use this app in a lecture we will need to configure the Console font size and colors.
4.1 Right click the title of the console application, once you execute it, and chose properties
4.2 go to the "Options" tab and mark the checkbook "QuickEdit Mode"
4.3 Go to the "Font" tab -> Chose size 20 (I think that 20 best fit for lecture, not for local use)
And we are ready to go :-)