sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

03/14/2017

适用于：Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance适用于：Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance

获取 Transact-SQLTransact-SQL 给定参数化类型 (简单或强制) 下的语句的 stmt_sql_handle。Gets the stmt_sql_handle for a Transact-SQLTransact-SQL statement under given parameterization type (simple or forced). 这使你可以通过在知道查询存储的文本时使用其 stmt_sql_handle 来引用其中存储的查询。This allows you to refer to queries stored in the Query Store by using their stmt_sql_handle when you know their text.

主题链接图标 Transact-SQL 语法约定 Topic link icon Transact-SQL Syntax Conventions

语法Syntax

sys.fn_stmt_sql_handle_from_sql_stmt   
(  
    'query_sql_text',   
    [ query_param_type   
) [;]

参数Arguments

query_sql_textquery_sql_text
要作为句柄的查询在查询存储中的文本。Is the text of the query in the query store that you want the handle of. query_sql_text 为 nvarchar (max)，无默认值。query_sql_text is a nvarchar(max), with no default.

query_param_typequery_param_type
查询的参数类型。Is the parameter type of the query. query_param_type 是 tinyint。query_param_type is a tinyint. 可能的值有：Possible values are:

NULL-默认值为0NULL - defaults to 0
0 - 无0 - None
1-用户1 - User
2-简单2 - Simple
3-强制3 - Forced

返回的列Columns Returned

下表列出了 sys.fn_stmt_sql_handle_from_sql_stmt 返回的列。The following table lists the columns that sys.fn_stmt_sql_handle_from_sql_stmt returns.

列名称Column name	类型Type	说明Description
statement_sql_handlestatement_sql_handle	varbinary(64)varbinary(64)	SQL 句柄。The SQL handle.
query_sql_textquery_sql_text	nvarchar(max)nvarchar(max)	语句的文本 Transact-SQLTransact-SQL 。The text of the Transact-SQLTransact-SQL statement.
query_parameterization_typequery_parameterization_type	tinyinttinyint	查询参数化类型。The query parameterization type.

返回代码值Return Code Values

0（成功）或 1（失败）0 (success) or 1 (failure)

备注Remarks

权限Permissions

要求对数据库具有 EXECUTE 权限，并对查询存储目录视图具有 DELETE 权限。Requires the EXECUTE permission on the database, and DELETE permission on the query store catalog views.

示例Examples

下面的示例执行一个语句，然后使用 sys.fn_stmt_sql_handle_from_sql_stmt 返回该语句的 SQL 句柄。The following example executes a statement, and then uses sys.fn_stmt_sql_handle_from_sql_stmt to return the SQL handle of that statement.

SELECT * FROM sys.databases;   
SELECT * FROM sys.fn_stmt_sql_handle_from_sql_stmt('SELECT * FROM sys.databases', NULL);

使用函数可以将查询存储数据与其他动态管理视图关联。Use the function to correlate Query Store data with other dynamic management views. 下面的示例：The following example:

SELECT qt.query_text_id, q.query_id, qt.query_sql_text, qt.statement_sql_handle,  
q.context_settings_id, qs.statement_context_id   
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_id  
CROSS APPLY sys.fn_stmt_sql_handle_from_sql_stmt (qt.query_sql_text, null) AS fn_handle_from_stmt  
JOIN sys.dm_exec_query_stats AS qs   
    ON fn_handle_from_stmt.statement_sql_handle = qs.statement_sql_handle;