Database Reference
In-Depth Information
in the
master
database. When a stored procedure with an
sp_
prefix is submitted for execution, SQL Server looks for
the stored procedure in the following places in the following order:
master
database
•
In the
•
In the current database based on any qualifiers provided (database name or owner)
dbo
as the schema, if a schema is not specified
Therefore, although the user-created stored procedure prefixed with
sp_
exists in the current database, the
master
database is checked first. This happens even when the stored procedure is qualified with the database name.
To understand the effect of prefixing
sp_
to a stored procedure name, consider the following stored procedure:
•
In the current database using
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[sp_Dont]')
AND type IN (N'P', N'PC') )
DROP PROCEDURE [dbo].[sp_Dont]
GO
CREATE PROC [sp_Dont]
AS
PRINT 'Done!'
GO
--Add plan of sp_Dont to procedure cache
EXEC AdventureWorks2012.dbo.[sp_Dont] ;
GO
--Use the above cached plan of sp_Dont
EXEC AdventureWorks2012.dbo.[sp_Dont] ;
GO
The first execution of the stored procedure adds the execution plan of the stored procedure to the procedure
cache. A subsequent execution of the stored procedure reuses the existing plan from the procedure cache unless a
recompilation of the plan is required (the causes of stored procedure recompilation are explained in Chapter 10).
Therefore, the second execution of the stored procedure
spDont
shown in Figure
19-12
should find a plan in the
procedure cache. This is indicated by an
SP:CacheHit
event in the corresponding Extended Events output.
Figure 19-12.
Extended Events output showing the effect of the sp_ prefix on a stored procedure name