TVP vs JSON vs XML as input parameters in SQL Server

I can’t think of any distinction between JSON and XML that is particularly relevant here. So it’s really TVPs vs structured string data (and I’ll just use JSON to mean either of them, or any other protocol of structured string data, in the rest of the answer).

The real problem with TVPs is their inflexibility and non-generality.

They require a user-defined table type to be declared separately from the header of the procedure – making it a hurdle for the procedure caller to look up the shape, and a hurdle for the procedure maker to alter the shape (which requires the withdrawal of the procedure, the alteration of the table type, then the reinstatement of the procedure).

They are in-only, so the technique of using JSON must remain for out parameters (and in turn, those are sometimes required because you can’t nest insert-execs).

And they tend to be more difficult to use when procedures are called from client-side languages (i.e. outside TSQL).

Often these seemingly-awkward features like TVPs cover some unusual circumstances, so it wouldn’t surprise me to find that TVPs perform better than JSON with very high data volumes. I might first be inclined to use a temp table, but then you potentially lose re-entrancy.

But for “a few columns and a few hundred rows”, nowadays I would settle on trying JSON first, simply because the same technique can be applied consistently to a larger variety of circumstances.