Wednesday, December 27, 2006

Don't start the user defined stored procedure with "SP_"

As you might be knowing the system stored procs would be prefixed with "SP_". If we prefix "sp_" in our user-defined stored procedure it would bring down the performance because SQL Server always looks for a stored procedure beginning with "sp_" in the following order:

1) Master DB,
2) The stored procedure based on the fully qualified name provided,
3) The stored procedure using dbo as the owner, if one is not specified.

So, when you have the SP with the prefix "sp_" in the DB other than master, the Master DB is always checked first, and if the user-created SP has the same name as a system stored proc, the user-created stored procedure will never be executed.

For example, Let's say that by mistake you have named one of your user defined stored procedure as "sp_help" within the database!

Create proc sp_help
as
Select * from dbo.empdetails


Now when you try executing the stored procedure using the below script you would realize that it has actually called the sp_help system stored proc of Master DB and not your SP.

Exec sp_help

Hope this helps!!

Technorati tags: , , ,

No comments: