Jovan Popovic has a couple of posts on JSON. First, using OPENJSON to generate a tally table:
Problem: I want to dynamically generate a table of numbers (e.g. from 0 to N). Unfortunately we don’t have this kind of function in SQL Server 2016, but we can use OPENJSON as a workaround.
OPENJSON can parse array of numbers [1,25,3,5,32334,54,24,3] and return a table with [key,value] pairs. Values will be elements of the array, and keys will be indexes (e.g. numbers from 0 to length of array – 1). In this example I don’t care about values I just need indexes.
Well, that’s one way to do it.
You might notice that table scans take majority of the query cost. Cost of the FOR JSON (JSON SELECT operator) is 0% compared to others. Also, since we are joining small tables (one sales order and few details), cost of the JOIN is minor. Therefore, if you processing small requests there will be no performance difference between formatting JSON on client side and in database layer.
This comment was actually due to a bug in the AdventureWorks CTP 3 database. The good news is that JSON isn’t obviously slow performance problems, but I’d like to see some more thorough performance tests.
Both posts via Database Weekly.