SQL Server Training | Tutorials | How to's

NO, user defined Stored Procedures, should not start with SP_, as this bring down the performance.

SQL Server looks for a stored procedure beginning with "sp_" in the following order:

1. MASTER system database
2. use FQN (Fully Qualified Name) to locate the SP
3. if FQN is not supplied, current database is used with dbo as owner

When we have the SP with the prefix "sp_" in the userdb, the master database is always checked first, this slow down the performance.

For example, if a user-created SP has the same name as a system stored proc, then system procedure will always take precedence and user-created stored procedure will never be executed.

Let’s understand this with a example.

SP_HELPDB, is a system stored procedure, which displays information about a specified database or all databases.

Let’s execute this procedure in master database

use master
go
sp_helpdb

OUTPUT

This displays information about databases as expected.

Now on the contrary, let’s create a SP_HELPDB as a user defined Stored procedure in a user defined database and see what happens

--- Creating a test user database
create database test
go
--- using test database
use TEST
go
----  creating a user defined stored procedure name sp_helpdb, which is also a system DB
create procedure sp_helpdb
as
begin
    select name from test.dbo.sysobjects
end

Lets’ try to execute this user defined stored procedure "test.dbo.sp_helpdb"

---- Executing a User Defined procedure 
exec test.dbo.sp_helpdb

OUTPUT

This is exactly the same out as we got in system store procedure, as before, that means a system store procedure is got executed not the user defined stored procedure.

Now, there is no way we can call the a user defined stored procedure "test.dbo.sp_helpdb"

This confirms that, SQL Server looks for a stored procedure beginning with "sp_" in the following order

1. MASTER system database
2. use FQN (Fully Qualified Name) to locate the SP
3. if FQN is not supplied, current database is used with dbo as owner

So we should avoid putting "SP_" user defined procedures. This will avoid lookup time required to search in master database, which will definitely increase execution time by few ms.

Tags : SQL Server looks for a stored procedure beginning with sp_, user defined procedure naming convention,sql server stored procedure execution phases, how stored procedure executes