I do have a scenario where I need to make a JSON in a batch for a million rows in a sql server table. Ideally my first inclination is to use the JSON Path as that gives me a clear JSON value which is good to maintain, soon enough I did realize JSON Path do have a performance cost it.

The sample code can be viewed in the Carbon here https://carbon.now.sh/vZgNoLSqX502nGIPDxmP

Verdict:

Using SQL Concatenation I was able to get the result in 3 seconds compared to 16 seconds on the JSON Path, thus I recommend to use SQL Concatenation for batch operations, where we are confident on the datatypes and ok to invest time in the maintenance later on (as the code looks a lot messier with string concat)

--

--

Surendra Ganti

Surendra Ganti

A techno functional expert in the financial domain, A Versatilist, with a lot of experience in developing on SQL Server. Thoughts and opinions are my own.