Export (0) Print
Expand All
Expand Minimize

sp_recompile (Transact-SQL)

Causes stored procedures and triggers 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 SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.

Topic link icon Transact-SQL Syntax Conventions


sp_recompile [ @objname = ] 'object'

[ @objname= ] 'object'

The qualified or unqualified name of a stored procedure, trigger, table, or view in the current database. object is nvarchar(776), with no default. If object is the name of a stored procedure or trigger, the stored procedure or trigger will be recompiled the next time that it is run. If object is the name of a table or view, all the stored procedures or triggers 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 and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

NoteNote

SQL Server automatically recompiles stored procedures and triggers when it is advantageous to do this.

Requires ALTER permission on the specified object.

The following example causes stored procedures and triggers that act on the Customer table to be recompiled the next time that they are run.

USE AdventureWorks2008R2;
GO
EXEC sp_recompile N'Sales.Customer';
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft