I’ve been thinking a lot lately about the incorporation of JSON into SQL Server. At first I see it as a technology to be abused. I can’t tell you how many stored procedures I have seen that had a single input parameter as an XML variable when XML was introduced to SQL Server. It was very popular in some circles because you didn’t have to change the signature of a stored procedure when new data elements were added to the communication. Conversely, it also did not fail to compile when you deprecated input variables, etc. This creates a lot of issues as an application matures, because the input is not type specific.
One thing XML was really good at was in sending in sets as parameters in TSQL. In 2008 they introduced the user defined table type, which allows you to instead pass a set as a single input parameter to a stored procedure or function, instead of using XML. The other advantage of using a table parameter over XML was not running XML readers as a separate thread, that when the read fails resulted in a memory leak. So, like a good consumer of Microsoft products, I found myself using user defined tables as input parameters. This works great when your application works in sets. You can simply pass the whole set to a stored procedure and it will merge the differences.
Recently I was debugging some SQL code for an application, and found that when you use ADO.Net and pass in a collection to an SQL Server Table Variable, behind the scenes it convers that simple two lines of ADO Code into a create table variable instance, and then a series of INSERT INTO @VAR VALUES for every record. While this isn’t the end of the world, it doesn’t seem to be very efficient.
So, today I am wondering how well JSON has been optimized in TSQL as a set data transport mechanism? I’m wondering if you have tried using JSON to pass sets to procedures, and what sort of performance you may be getting? Would you recommend passing sets through JSON, user defined table variables, or XML? Drop a comment with your experience.
Cheers,
Ben