c# - SqlDataReader 和 SQL Server 2016 FOR JSON 将 json 拆分为 2k 字节的 block

Question

最近我研究了 Azure SQL 数据库的新for json auto 功能。

例如，当我使用此查询选择大量记录时:

Select
    Wiki.WikiId
    , Wiki.WikiText
    , Wiki.Title
    , Wiki.CreatedOn
    , Tags.TagId
    , Tags.TagText
    , Tags.CreatedOn
From
    Wiki
Left Join
    (WikiTag
Inner Join 
    Tag as Tags on WikiTag.TagId = Tags.TagId) on Wiki.WikiId = WikiTag.WikiId
For Json Auto

然后使用 C# SqlDataReader 进行选择:

var connectionString = ""; // connection string
var sql = "";  // query from above
var chunks = new List<string>();

using (var connection = new SqlConnection(connectionString)) 
using (var command = connection.CreateCommand()) {
    command.CommandText = sql;
    connection.Open();

    var reader = command.ExecuteReader();

    while (reader.Read()) {
            chunks.Add(reader.GetString(0)); // Reads in chunks of ~2K Bytes
    }
}

var json = string.Concat(chunks);

我得到了很多数据 block 。

为什么我们有这个限制？为什么我们不把所有东西都集中在一起？

当我阅读 nvarchar(max) 专栏时，我会把所有内容都放在一个 block 中。

感谢解释

Answer 1

来自 Format Query Results as JSON with FOR JSON :

Output of the FOR JSON clause

The result set contains a single column.

A small result set may contain a single row.

A large result set splits the long JSON string across multiple rows. By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. The SSMS status bar displays the actual row count.

Other client applications may require code to recombine lengthy results into a single, valid JSON string by concatenating the contents of multiple rows. For an example of this code in a C# application, see Use FOR JSON output in a C# client app.

我会说这完全是出于性能原因，类似于 XML。更多 SELECT FOR XML AUTO and return datatypes和 What does server side FOR XML return?

In SQL Server 2000 the server side XML publishing - FOR XML (see http://msdn2.microsoft.com/en-us/library/ms178107(SQL.90).aspx) - was implemented in the layer of code between the query processor and the data transport layer. Without FOR XML a SELECT query is executed by the query processor and the resulting rowset is sent to the client side by the server side TDS code. When a SELECT statement contains FOR XML the query processor produces the result the same way as without FOR XML and then FOR XML code formats the rowset as XML. 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. 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.