User Store Procedure and their best position in Database
-
Hi,
That's my question.......
I have created a series of procedure, where should be located (master, user DBs...etc....) and if possible to explain the reason (eg performance...), for that....
If someone could link me to good web pages where to find more about this......
Thank........
-
Generally DB specific SP's live in the DB they are for. Common SP's can live in master or a COMMON user DB. Regardless of where you put them NEVER (did I mention NEVER) call them sp_something.
1. SQL Will ALWAYS go to master 1st to find SP
2. Can get confusing with "What procedures are mine"?
3. Is a general WORST practice
Good Hunting!AJ Ahrens
webmaster@kritter.net -
... With the execption of administrative stored proc that need to do work in many dbs, but not necessarely all at the same time. It can be a good idea to use master then. But other than that there's no real good reason to put them in master.
-
To expand on AJ's comment, I usually call my user stored procedures (the one's that exist in non-system databases) usp_something, usp standing for User Stored Procedure.
-
http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank for your sugestion......and links....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply