| title | sp_recompile (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/16/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine, sql-database | ||
| ms.reviewer | |||
| ms.technology | system-objects | ||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 6192ca87-febd-4075-8199-14b4fa609b8c | ||
| author | stevestein | ||
| ms.author | sstein | ||
| monikerRange | =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]
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. In a [!INCLUDEssSqlProfiler] collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.
Transact-SQL Syntax Conventions
sp_recompile [ @objname = ] 'object' [ @objname= ] 'object'
The qualified or unqualified name of a stored procedure, trigger, table, view, or user-defined function in the current database. 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.
0 (success) or a nonzero number (failure)
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.
Note
[!INCLUDEssNoVersion] automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous to do this.
Requires ALTER permission on the specified object.
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
CREATE PROCEDURE (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
System Stored Procedures (Transact-SQL)