I have heard that I should not use sp_ at the beginning of stored procedure names. I'm wondering why not as this seems like a great convention for those objects.
asked
Using sp_ in procedure names
See this
http://msdn.microsoft.com/en-us/library/ms190669%28SQL.90%29.aspx
"We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures. The name you choose may conflict with some future system procedure. If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own."
I agree it's better to avoid this situation, but it's also yet another reason to use two-part names (schema.object). For more on that, see this other question:
http://ask.sqlservercentral.com/questions/1784/dbo-database-owner-in-select-statement.
We use a standardised naming convention for all objects we create in any databases we administer. It ensures that our objects dont have any chance of colliding with system developments from 3rd party vendors or Microsoft and it ensures they also sort nicely if you view them in the SSMS object browser or can be located in system tables ... we use cx_usp_ProcedureName, cx_uvw_ViewName, cx_utr_TriggerName (for procedures, views and triggers respectively) and so on for other objects.
When reading code you can easily get into the groove of seeing sp_... for system procedures and cx_usp... for homegrown ones
Jonathan
There is also a small performance hit (if you dont fully qualify the sp when calling it).
SQL Server will search the master database 1st to see if it exists there and then it will search the user database
From day 1 I have been naming my stored procedures using "usp_" (user stored procedure).
