Query/management HTTP response

Response status

The HTTP response status line follows the HTTP standard response codes. For example, code 200 indicates success.

The following status codes are currently in use, although any valid HTTP code may be returned.

Response headers

The following custom headers will be returned.

Response body

If the status code is 200, the response body is a JSON document that encodes the query or management command’s results as a sequence of rectangular tables. See below for details.

If the status code indicates a 4xx or a 5xx error, other than 401, the response body is a JSON document that encodes the details of the failure. For more information, see Microsoft REST API Guidelines. For more information, see Microsoft REST API Guidelines.

JSON encoding of a sequence of tables

The JSON encoding of a sequence of tables is a single JSON property bag with the following name/value pairs.

The Table property bag has the following name/value pairs.

The Column property bag has the following name/value pairs.

The Row array has the same order as the respective Columns array. The Row array also has one element that coincides with the value of the row for the relevant column. Scalar data types that can’t be represented in JSON, such as datetime and timespan, are represented as JSON strings.

The following example shows one possible such object, when it contains a single table called Table_0 that has a single column Text of type string, and a single row.

{
    "Tables": [{
        "TableName": "Table_0",
        "Columns": [{
            "ColumnName": "Text",
            "DataType": "String",
            "ColumnType": "string"
        }],
        "Rows": [["Hello, World!"]]
}

Another example:

The meaning of tables in the response

In most cases, management commands return a result with a single table, containing the information generated by the management command. For example, the .show databases command returns a single table with the details of all accessible databases.

Queries generally return multiple tables. For each tabular expression statement, For each tabular expression statement, one or more tables are generated in-order, representing the results produced by the statement.

Three tables are often produced:

• An @ExtendedProperties table that provides additional values, such as client visualization instructions (information provided by the render operator), instructions (information provided by the render operator), information about the query’s effective database cursor, information about the query’s effective database cursor, or information about the query’s effective use of the query results cache. or information about the query’s effective use of the query results cache.

  For queries sent using the v1 protocol, the table has a single column of type string, whose value is a JSON-encoded string, such as:

  Value
  {“Visualization”:“piechart”,…}
  {“Cursor”:“637239957206013576”}

  For queries sent using the v2 protocol, the table has three columns: (1) An integer column called TableId indicating which table in the results set the record applies to; (2) A string column called Key indicating the kind of information provided by the record (possible values: Visualization, ServerCache, and Cursor); (3) A dynamic column called Value providing the Key-determined information.

  TableId	Key	Value
  1	ServerCache	{“OriginalStartedOn”:“2021-06-11T07:48:34.6201025Z”,…}
  1	Visualization	{“Visualization”:“piechart”,…}

• A QueryStatus table that provides additional information about the execution of the query itself, such as, if it completed successfully or not, and what were the resources consumed by the query.

  This table has the following structure:

  Timestamp	Severity	SeverityName	StatusCode	StatusDescription	Count	RequestId	ActivityId	SubActivityId	ClientActivityId
  2020-05-02 06:09:12.7052077	4	Info	0	Query completed successfully	1	…	…	…	…

  Severity values of 2 or smaller indicate failure.

• A TableOfContents table, which is created last, and lists the other tables in the results.

  An example for this table is:

  Ordinal	Kind	Name	Id	PrettyName
  0	QueryResult	PrimaryResult	db9520f9-0455-4cb5-b257-53068497605a
  1	QueryProperties	@ExtendedProperties	908901f6-5319-4809-ae9e-009068c267c7
  2	QueryStatus	QueryStatus	00000000-0000-0000-0000-000000000000