SQL Server considers Stored Procs prefixed by “sp_” as System Stored Procedures and while executing, SQL server looks for the SP in master database and then, in the current database.
This has 2 disadvantages.
(i) Additional verification for the SP in master db causes overhead.
(ii)If it happens that SP with same name exists in master db, SP in master db will be executed.
Solutions:
DO NOT CREATE SPs that starts with “sp_”
If already SPs had been created, whose names start with “sp_” and application already using them, Ensure that calling those Stored Procedures are always qualified by dbname.owner.SP
Exec mydb.dbo.sp_test
No comments:
Post a Comment