Prefix: sp, sp_, or not...

[ISPrincess]

I have read articles stating NOT to use the 'sp' prefix for stored procedures.
I have read articles stating NOT to use the 'sp_' prefix for stored procedures.

I do not know if there is difference between the way SQL handles 'sp' vs 'sp_' stored procs or not.

I am not sure it is wise to use either of these and instead start your stored proc name with an indication of the project. (AP, AR, etc).

I would appreciate thoughts, but really need backup documentation on this, esp. from Microsoft.

So if anyone can give me any links to articles by Microsoft or another forum with regard to use of 'sp' vs 'sp_' prefix, I would really appreciate it.

Thank you!


PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[hmckillop]

I have had a microsoft consultant work with us for the last 6 months. There are many varying conventions, but we use ufn_ for functions and usp_ for procedures.
You shouldnt really use sp_ as Microsoft use this prefix for internal Stored Procs and you may get confused with what you created and they created.

I dont think though it makes any difference from a performance point of view.

 

[osjohnm]

Hi

If you use the sp_ as a prefix for procedures and you execute the procedure in one of your user databases, SQL Server will first attempt to find the procedure in the master database and then the user database. THis would affect performance but you wouldn't even notice it.

I don't remember where I found the article about this but I personally wouldn't recommend the use of sp_.

Hope this helps

John

 

[osjohnm]

Hi

Here is the link to the article:

http://www.sql-server-performance.com/stored_procedures.asp

John

 

[ISPrincess]

Again, mainly, I am asking the difference between using 'sp' vs 'sp_'.

What about using 'sp' WITHOUT THE UNDERSCORE.

PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[JayKusch]

Use sp! NOT sp_

Thanks

J. Kusch

 

[JamesLean]

ISPrincess,
The only technical reason you shouldn't use the sp_ prefix is the one osjohnm gave.

Other than that, it's just a matter of convention. Stored procs prefixed with sp_ are recognised as system stored procs by SQL users. If you're glancing down a list of procs your going to ignore all the ones that start with sp_ (or for that matter sp).

I use an "ap" prefix for all my user procs. Others have their own conventions. You could use sp but it may be slightly confusing for anyone who inherits the database from you.

--James