asked
SP prefix solution
As i know SP_prcedure name are reserved for system procedure, somhow i have just come on this database project and found there are more than 50 SP are already made by someOne by sp_ prefix. i want to know what possible wrong with that can happen..? i know need to avoid this prefix so i sud worry and do something or let it go as it is... and this datbase already on live production.
Read this blog post: [sp_ prefix, myth or fact][1] In the tests, he proved the sp_ prefix was slower (the other prefix was about 15% faster). [1]:
http://sqlserverpedia.com/blog/sql-server-bloggers/stored-procedure-performance-using-%E2%80%9Csp_%E2%80%9D-prefix-%E2%80%93-myth-or-fact/
In addition to sp_ being slower, it can also potentially make your SP unavailable. As far as I've been taught, if there is an SP in master with the same name as your local SP in your user-database, there's a way to execute the local SP. Even if you use the three-part-name databasename.dbo.sp_theproc, SQL Server will first look to see if there's an SP in master with that name, and if there is, it will execute the master stored proc in the database context you've as databasename in the three-part name. Here's a script that shows just this. USE myLocalDB GO CREATE PROC dbo.sp_help AS PRINT 'This is the local version of sp_help' GO EXEC dbo.sp_help GO EXEC myLocalDB.dbo.sp_help GO
thanks somewhere it is exactly what i want to know. now i can add this to in my plan as well to change the name after checking all other things. but one last question in my mind is same what is written in blog.. "do we really know this to be truth, or do have we simply taken someone’s word on it, treated it as sacred cow, or simply restated it for years it because it sounds good"..cause for me its a person blog view only.?
He made a test to verify this as a fact, but on the other hand, if you want to have a supported solution, you need to follow the recommendations from Microsoft.
It is good that you don't take everything at face value, but if you want to know, then test it yourself - only then will you be sure that what you know is true.
I've updated my answer with very simple example code to prove my point about the SP becoming unavailable if an SP with the same name is ever created in master - which might happen in a hotfix, service pack or new release, very, very often they are also undocumented.
As far as I'm aware, SP_ doesn't stand for 'stored procedure' at all and was put in by Sybase a long time ago to mean 'Special'. It has long been mistaken for an early bit of reverse-hungarian notation. It designates an object as being a special system object in the master database. I seem to remember Kalen Delaney saying in a presentation that you can prefix a table with sp_ as well and have some weird effects happen. In Sybase, all objects prefixed with sp_ are searched in the master as well as the current database. Procedures created without this prefix, are expected to be stored exclusively in the user's current database. It would be great if someone could do some research on this topic, because I'm a bit unsure of what actually happens.
I hadn't, and I think he's probably right but I think it applies to any object, including tables.
I must admit I cant get myself worked up over the performance issue (maybe its the fact I dont support hugely stressed or busy servers) I just follwo the same rules so that there is order and predicatbility in the environment.
