sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)
适用于: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 Database
Azure SQL 数据库Azure SQL Database
Azure SQL 托管实例Azure SQL Managed Instance
Azure SQL 托管实例Azure SQL Managed Instance
SQL Server 2016 (13.x)SQL Server 2016 (13.x)
SQL Server 2016 (13.x)SQL Server 2016 (13.x)
Azure SQL 数据库Azure SQL Database
Azure SQL 数据库Azure SQL Database
Azure SQL 托管实例Azure SQL Managed Instance
Azure 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 语法约定
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;
另请参阅See Also
sp_query_store_force_plan (Transact-sql) sp_query_store_force_plan (Transact-SQL)
sp_query_store_remove_plan (Transct-SQL) sp_query_store_remove_plan (Transct-SQL)
sp_query_store_unforce_plan (Transact-sql) sp_query_store_unforce_plan (Transact-SQL)
sp_query_store_reset_exec_stats (Transact-sql) sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_flush_db (Transact-sql) sp_query_store_flush_db (Transact-SQL)
sp_query_store_remove_query (Transact-sql) sp_query_store_remove_query (Transact-SQL)
查询存储目录视图 (Transact-sql) Query Store Catalog Views (Transact-SQL)
使用查询存储来监视性能Monitoring Performance By Using the Query Store