I tend to dislike the prefix concept. Stored Procedures are all grouped together anyway. We all know not to use SP_, so the lack of that prefix is a standard in itself.
I like the rest of the idea and carry it a bit further. I want the name of the SP to tell me not only what table(s) it's accessing but what it's purpose is. That gives me clues as to whether I can extend it or use it elsewhere.
A SP named GetSalesReportInfoBySalesRep tells me a lot more than usp_Sel_Sales. In the second case, can I use the SP elsewhere in my program without expecting it to get modified later? Can I add columns to the report without breaking things?
I also comment the SP pretty clearly on the inside. I put it's complete purpose as designed and even a date and where used, although I know that will be out of date and incomplete eventually.
I guess I name SPs the way I name variables. Long, descriptive and as specific as possible. In VB, where my front ends are almost always written, long doesn't matter since IntelliSense always fills in the blanks anyway. (Well, not for SPs but for a lot of things.)
I can divide my stored procedures to operate in different parts of the DB (CRM, Statistics, Reporting etc.). That's a module (call it whatever you want) - Mark Sandy
This is where some of the confusion comes in with naming conventions. Mark's standard is great for him as an application developer since he is concerned with the individual modules and the stored procedures that go with them. What about the database developer though? Or even another application working on a different module that needs to make a change to the Customer table? It's much more difficult for these folks to identify the procedures they need to touch to make sure the impact of their change is minimized.
Is there a naming convention that covers all situations? Probably not.
We use Mark Sandy's approach as well. Many of our stored procs deal with one table, and so the Module_Table_Action has worked EXTREMELY well. If you want to see which procs are acting on a table, its very easy to find.
Even better, when training someone else on the architecture, it takes all of 5 minutes for them to comprehend - the ultimate in sustainability.
Since I also code on the client side, its made that part simpler as well.
The last thing I'll mention is that the unique identifier for a table I always make the table name + "ID", e.g., CustomerID for the Customer table. Makes running queries by string concatenation really useful. Once I know my table name, I know that the proc to retrieve information is RPO_[TableName]_GET, based off of ID [TableName]ID.
I can't say enough good things about this technique.
So whats so extreme about conventions for naming of 'system created constraints'?
It's damnably hard work to issue database upgrade scripts from Dev --> QA --> Live without having standard, explicit constraint names...
With the correct scripting or migration tools, explicit constraint names aren't necessary. I don't object to constraint naming conventions - but I find that for a team of developers and dbas, they are difficult to enforce. I try to focus on tables, views, stored procedures, and other major object types instead. If you have the environment control to keep constraint-level names standardized - I envy you.
I know it's almost universal to have Verb-first, then the Object as a naming convention:
But I use the reverse -- Object-first, then Verb -- for a specific reason
The reason is that when they are listed alphabetically (as they are everywhere) I want to see all of the User-oriented sprocs together, then all of the Inventory-oriented sprocs together, etc.; rather than all of the Delete sprocs together, then all of the Insert sprocs together, etc.
The prefix is helpful, but only in certain conditions.
If you're using the Query Analyzer object viewer or using Enterprise Manager to browse for your stored procedures, then a prefix probably won't help you much. I don't think it would make it more difficult either.
Remember that the naming convention for stored procs is for more than just the DBA though. Developers use these stored procs and in a complex script with many stored procs, tables, views, functions, and other objects, it takes out the ambiguity when looking at a script. It's especially helpful if you're looking at the script via a text editor, Visual Studio, or something else where you don't have the full support of Query Analyzer or the ability to quickly run a SQL statement every time you see an object name and can't remember what it is. It won't make or break you, it just makes development easier if you can limit the guesswork.
Good article though. I'm a big supporter of naming conventions. I have used several different ones at various client sites and the ones who have a standard and follow it are much easier to support than those who don't -- irregardless of whether they include or discard the prefix.