10K Request per Second: REST API with Azure SQL, Dapper and JSON

Hi marekott ,

you're absolutely right, if you have to return a big JSON, you need to manually concatenate the results as they will be split in more than one row. I'll make sure to call this out in the GitHub repo, so everyone will be aware.

Here's the doc page where this behavior, along with the technique to deal with this, is explained: https://docs.microsoft.com/en-us/sql/relational-databases/json/use-for-json-output-in-sql-server-and-in-client-apps-sql-server?view=sql-server-ver15#use-for-json-output-in-a-c-client-app.

Another option is to use a Dapper extension that my colleague Jovan created: https://github.com/JocaPC/Dapper.Stream#creating-json-rest-api.

Last option (or maybe "trick") is to put the JSON into a variable right on the server side, so you will not have to deal with JSON concatenation:

DECLARE @j NVARCHAR(MAX) = (SELECT * FROM <Table> FOR JSON AUTO)
SELECT @j AS JSON