sp_recompile (Transact-SQL)sp_recompile (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

导致存储过程、触发器和用户定义函数在下次运行时重新编译。Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. 具体方法是:从过程缓存中删除现有计划,强制在下次运行该过程或触发器时创建新计划。It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. SQL Server ProfilerSQL Server Profiler 集合中,记录事件 SP:CacheInsert 而不是事件 SP:Recompile。In a SQL Server ProfilerSQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.

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

语法Syntax

  
sp_recompile [ @objname = ] 'object'  

参数Arguments

[ @objname=] '对象[ @objname= ] 'object'
当前数据库中存储过程、触发器、表、视图或用户定义函数的限定或未限定的名称。The qualified or unqualified name of a stored procedure, trigger, table, view, or user-defined function in the current database. 对象nvarchar(776) ,无默认值。object is nvarchar(776), with no default. 如果对象是存储过程、 触发器或用户定义函数,该存储过程,触发器的名称或函数将运行在下一步时重新编译。If object is the name of a stored procedure, trigger, or user-defined function, the stored procedure, trigger, or function will be recompiled the next time that it is run. 如果对象是表或视图中,所有存储过程、 触发器名称或引用表或视图的用户定义函数将它们运行在下一步时重新编译。If object is the name of a table or view, all the stored procedures, triggers, or user-defined functions that reference the table or view will be recompiled the next time that they are run.

返回代码值Return Code Values

0(成功)或非零数字(失败)0 (success) or a nonzero number (failure)

备注Remarks

sp_recompile 只在当前数据库中寻找对象。sp_recompile looks for an object in the current database only.

存储过程或触发器和用户定义函数使用的查询仅在编译时优化。The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. 对数据库进行了索引或其他会影响数据库统计的更改后,已编译的存储过程、触发器和用户定义函数可能会失去效率。As indexes or other changes that affect statistics are made to the database, compiled stored procedures, triggers, and user-defined functions may lose efficiency. 通过对作用于表上的存储过程和触发器进行重新编译,可以重新优化查询。By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

备注

SQL ServerSQL Server 会在便利时自动对存储过程、触发器和用户定义函数进行重新编译。automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous to do this.

权限Permissions

需要具有对指定对象的 ALTER 权限。Requires ALTER permission on the specified object.

示例Examples

下面的示例使作用于 Customer 表的存储过程、触发器和用户定义函数在下次运行时重新编译。The following example causes stored procedures, triggers, and user-defined functions that act on the Customer table to be recompiled the next time that they are run.

USE AdventureWorks2012;  
GO  
EXEC sp_recompile N'Sales.Customer';  
GO  

请参阅See Also

CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)