May 20, 2011 at 11:33 am
Hello,
I have a SQL 2000 database that I have inherited and ALL of the stored procedure names are prefixed with "sp_" -- I guess this was before the prefix became "reserved" (or has it?) -- hence my question:
Any issues with upgrading SQL 2000 to 2008 when all the stored procedure names are prefixed with "sp_"?
Thanks in advance
May 20, 2011 at 11:36 am
It's not really "reserved", and the procedures will still work in 2005 or 2008. It's just a bad idea, because it can have performance issues while SQL Server looks in the master database for the proc and doesn't find it.
It's more about making things fast than it is about making them work at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2011 at 12:35 pm
I also like to keep a distinction between system stored procedures and non-system stored procedures. I have a few utility stored procedures that are in the master database, and I wouldn't want them confused with system ones.
May 20, 2011 at 1:26 pm
Ok -- thanks,
With regard to "making things faster" -- what do people normally do -- rename every stored procedure and then go through all the code and rename as well? -- what other recourse is there?
Seems strange to me that for a user database SQL searches the system stored procedures first and then the user database stored procedures -- given a connection to a users database, would it not make sense to check for the stored procedure name in the user database first?
Forgive me Obi-Wan, but I am just an apprentice ...
May 20, 2011 at 1:33 pm
bdaviduck (5/20/2011)
With regard to "making things faster" -- what do people normally do -- rename every stored procedure and then go through all the code and rename as well?
No. They leave it as is. The minuscule performance difference is not worth the cost of changing all the code and dealing with all the resultant bugs.
Seems strange to me that for a user database SQL searches the system stored procedures first and then the user database stored procedures -- given a connection to a users database, would it not make sense to check for the stored procedure name in the user database first?
For normal stored procs is does, but sp_ means System Procedure and hence gets a lookup to the resource DB (not master) first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 1:34 pm
The whole point of the sp_ prefix is to tell SQL Server to look in master first. It gives a slight performance improvement on applications that use the system procedures more heavily. Almost anything else, even usp_ (which some shops do use) bypasses that, and looks in the user database first.
As for handling it, either eat the performance hit, or rename them. No other way to deal with it that I know of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2011 at 1:42 pm
Take the performance hit - it is really tiny as Gail said. I would then look at implementing a policy for proc naming conventions for new procs. You can set that so that the proc on creation or change must be a particular name format. Problem with that is that if a current proc is changed, it will have to adhere to naming policy and then you would need to change that name in the proc and in everything that references. Which could create a cascading effect of changes.
Just some food for thought.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2011 at 1:47 pm
As an FYI, just having sp_ (as Gus said) does not a system stored proc make. There are more requirements. You can read here for info on that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply