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

適用於: 是SQL Server 是Azure SQL Database 否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 會自動執行這個動作。SQL ServerSQL Server automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous to do this.

PermissionsPermissions

需要指定之物件的 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)