Database Reference
In-Depth Information
Recompilation Because of a Regular Table
To understand the query recompilation issue by creating a regular table within the stored procedure, consider the
following example:
IF (SELECT OBJECT_ID('dbo.TestProc')
) IS NOT NULL
DROP PROC dbo.TestProc;
GO
CREATE PROC dbo.TestProc
AS
CREATE TABLE dbo.ProcTest1 (C1 INT); --Ensure table doesn't exist
SELECT *
FROM dbo.ProcTest1; --Causes recompilation
DROP TABLE dbo.ProcTest1;
GO
EXEC dbo.TestProc; --First execution
EXEC dbo.TestProc; --Second execution
When the stored procedure is executed for the first time, an execution plan is generated before the actual
execution of the stored procedure. If the table created within the stored procedure doesn't exist (as expected in the
preceding code) before the stored procedure is created, then the plan won't contain the processing strategy for the
SELECT
statement referring to the table. Thus, to execute the
SELECT
statement, the statement needs to be recompiled,
as shown in Figure
17-9
.
