Add a minimal reproducible example to your question. It's highly unlikely that there is a 2KB limit imposed on json output from SQL Server.

@MaxVernon My understanding is that there is no real limit, it's just that the result is broken up into 2KB chunks. However, I seem to be having trouble finding any documentation that supports this.

It's easily provable that SQL Server returns more than 2KB JSON output. ;WITH src (n) AS (SELECT n = sc1.name FROM sys.syscolumns sc1 FOR JSON AUTO) SELECT json_length = LEN(src.n) FROM src;

@MaxVernon If you execute the following query, you will find that the result will be split into ~2kb chunks SELECT n = sc1.name FROM sys.syscolumns sc1 FOR JSON AUTO

I see that returns a string that is 20,528 bytes in length on my test database.

in my master database, it returns a single json string that is around 375k

@MaxVernon While the length is in fact 20,528 bytes, running the select statement alone returns 10 rows of ~2000 characters each. It could be possible that there may be a configuration difference, but I am running SQL server on its default settings.

If you can, create a minimal reproducible example that shows how the json is being chunked.

I will do my best @MaxVernon

I just tested it on vanilla SQL Server 2017 on Windows, and SQL Server 2019 on Linux. I have no non-standard configurations, and the select inside my CTE returns only a single row, i.e. not chunked.

@joeldesante - I'm genuinely interested in seeing how this works, and what you're seeing.

@MaxVernon I have updated the question to show what I am seeing. However, I can send some screenshots here too.

@joeldesante cool

what Client program is that? SQL Server Management Studio?

So as we can see, this is the result we expect. I am using TablePlus for Mac OS

perhaps it's TablePlus doing that. Can you try it with SSMS somewhere?

And above we have 11 rows

It's not table plus, I have the same result when using PDO in PHP

@joeldesante yah, I noticed that. It's completely not like that on the two servers I tried it on. Of course, I'm using SSMS though.

It might be that SSMS accounts for the chunking?

what is the version of SQL Server? SELECT @@VERSION

2019

that is the exact version I'm using too

except I'm on RedHat

I'm not sure if that would make much of a difference in this case.

just trying to eliminate possibilities

Im mostly concerned because there is no documentation to support this on Microsofts side

My sources are just one rouge Microsoft Project manager on stackoverflow.

And that's all I could find

Try declaring a string variable and SET it to the result of the SELECT, then SELECT that variable as the last statement. This worked for us when we encountered something similar

I looked at the docs site too, but can only see inferences that support json being an nvarchar(max)

this looks interesting, too, although it's mostly about FOR XML.

so, does this return the entire json value, @joeldesante ?:

I will test it now

that's what @AndriyM was suggesting. It seems like this might be a client-side issue where chunks are being automatically re-assembled by SSMS client-side, but they are not being reassembled in TablePlus, or php.

That worked for some reason.

My original solution was to just reassemble the strings via concatenation in PHP.

But I imagine this is less prone to error.

your question, and some good details from here in your answer would be very helpful for future visitors with this issue.

@MaxVernon Why exactly does this solution work?

I assume it just takes the result and assigns it to a variable which won't "chunk up" when being sent?

it's the client that is not automatically recompiling the chunked output into a single row. SQL Server is sending 2kb chunks to the client, and if the client is SSMS those chunks are automatically reassembled into a single row. This Microsoft page documents how and why the server does that: docs.microsoft.com/en-us/archive/blogs/sqlprogrammability/…

Thank you!

no worries. This is probably the pertinent bit I'd include in an answer:

> FOR XML does steaming XML formatting of the resulting rowset and directly sends its output to the server side TDS code in small chunks without buffering whole XML in the server space. The chunk size is 2033 UCS-2 characters. Thus, XML larger than 2033 UCS-2 characters is sent to the client side in multiple rows each containing a chunk of the XML.

Updating it now

> SQL Server uses a predefined column name for this rowset with one column of type NTEXT - “XML_F52E2B61-18A1-11d1-B105-00805F49916B” – to indicate chunked XML rowset in UTF-16 encoding. This requires special handling of the XML chunk rowset by the APIs to expose it as a single XML instance on the client side. In ADO.Net, one needs to use ExecuteXmlReader, and in ADO/OLEDB one should use the ICommandStream interface.

This would appear to be the column name used when sending chunked json: JSON_F52E2B61-18A1-11d1-B105-00805F49916B

the chunking is done to avoid SQL Server having to format the entire, possibly very large, json or xml object in server-memory as a single object.

I think the above sentences are also going to be useful

> For maximum XML publishing performance FOR XML does steaming XML formatting of the resulting rowset and directly sends its output to the server side TDS code in small chunks without buffering whole XML in the server space. The chunk size is 2033 UCS-2 characters. Thus, XML larger than 2033 UCS-2 characters is sent to the client side in multiple rows each containing a chunk of the XML.

Explains the reasoning behind this which is what I typically look for

agreed

you might do well to specify that this is only visible when the client does not reassemble the chunks. Specifically, you won't ever see this behavior in SQL Server Management Studio.

Alright, will do

@MaxVernon dba.stackexchange.com/a/279614/218594

@joeldesante nice. And if you felt like it, you could simplify the question to make it more generic, and less about the joins, since they are not actually the problem. Either way, that's a good answer.