SQL Server Query Store: [sys.query_store_query_text].[query_sql_text] is truncated?

Question

I'm working with the following query against the Query Store:

SELECT 
TOP 100
    qp.last_execution_time [qp__last_execution_time],
    qt.query_sql_text,
    len(qt.query_sql_text) sql_length,      
    round(rs.avg_duration ,5) avg_duration
    ,max_rowcount,avg_rowcount,last_rowcount
FROM 
    sys.query_store_plan qp
    INNER JOIN sys.query_store_query         [q]        ON qp.query_id = q.query_id
    INNER JOIN sys.query_store_query_text    [qt]       ON q.query_text_id = qt.query_text_id
    INNER JOIN sys.query_store_runtime_stats [rs]       ON qp.plan_id = rs.plan_id
WHERE
order by len(qt.query_sql_text) desc

What I'm noticing is that it seems the [query_sql_text] is being truncated, for example a typical statement will end with:

CAST( '20170920 00:00:00' AS datetime),CAST( '20140701 00:00:

Possibly relevant:
The SQL in this case is being generated by Power BI.
The len(qt.query_sql_text) is not maxed out at the same value when text is truncated.
I am viewing the SQL by copy/pasting from SSMS results pane into notepad.

SQL Version: Microsoft SQL Azure (RTM) - 12.0.2000.8 Aug 29 2017 13:06:11

I recall in some other "system tables" that SQL Server truncates SQL.

So the question is: can anyone say definitively whether [sys.query_store_query_text].[query_sql_text] can in some cases be truncated by SQL Server itself?

The alternative possibility, truncation happening in the client application, doesn't make any sense as it has to send a complete, valid SQL statement to SQL Server, otherwise how could it be executed? And yet, the variance in len(query_sql_text) rules out exceeding a column length limitation.

What might be going on here?

Answer 1

SSMS will truncate blob results when returning data to the data grid. You can modify the length in the Query Options, or return the data as XML. EG

SELECT 
TOP 100
    qp.last_execution_time [qp__last_execution_time],
    qt.query_sql_text,
    len(qt.query_sql_text) sql_length,      
    round(rs.avg_duration ,5) avg_duration
    ,max_rowcount,avg_rowcount,last_rowcount
FROM 
    sys.query_store_plan qp
    INNER JOIN sys.query_store_query         [q]        ON qp.query_id = q.query_id
    INNER JOIN sys.query_store_query_text    [qt]       ON q.query_text_id = qt.query_text_id
    INNER JOIN sys.query_store_runtime_stats [rs]       ON qp.plan_id = rs.plan_id
order by len(qt.query_sql_text) desc
for xml path

Or to get each query in a separate XML column:

SELECT 
TOP 100
    qp.last_execution_time [qp__last_execution_time],
    ( select qt.query_sql_text for xml path, type ) query_sql_text,
    len(qt.query_sql_text) sql_length,      
    round(rs.avg_duration ,5) avg_duration
    ,max_rowcount,avg_rowcount,last_rowcount
FROM 
    sys.query_store_plan qp
    INNER JOIN sys.query_store_query         [q]        ON qp.query_id = q.query_id
    INNER JOIN sys.query_store_query_text    [qt]       ON q.query_text_id = qt.query_text_id
    INNER JOIN sys.query_store_runtime_stats [rs]       ON qp.plan_id = rs.plan_id

order by len(qt.query_sql_text) desc

Or get the query text from the XEvents UI in SSMS.