Naming conventions are difficult to make useful ALL OF THE TIME. I'm currently working at a place that uses a strict naming convention. Our SPs are named by project_method_action_sp. Unfortunately when they have something common like adding a customer they do not have a good way to use the above naming convention. And my personal feeling is that the suffix _sel_sp is just rediculous as you will find an sp that the developer may have thought would be a simple select but in actuality does much more so the _sel_sp has no meaning at all. Also, why bother with the suffix _sp? I do like having the SPs grouped by project and method. But for the generic ones I would go with something along the lines of global_customer_add. That way you can find the reusable ones very easily and the project specific ones will be grouped together as well. My biggest gripe with our current method is that there is no correlation between the functional spec "method" name and the stored procedure name. This makes it very hard to validate that the functional spec has been correctly implemented.
Having a naming standard is great. But it can be taken too far. For instance we have a standard that states that all variable names, stored procedure names, function names, temp/variable tables have to be in lower case and all their field names in lower case yet the table names and field names of regular tables have to be mixed (are you confused yet? I know I get that way when I'm coding trying figure out what case I need to be using!). They also don't allow the use of aliases on the tables unless used in a subquery. All in all the coding standards they have set up make for a lot of bloated code and extra time spent coding. But since their code review process is also very strict the code is pretty and formatted the same way!
A comment on prefixes. If you use the <project>_<Method>_<action> and do your coding in Visual Studio Enterprise edition with a database project you can access the SPs in the database by hitting the first letter on your keyboard in the server explorer panes tree view. If all your SPs use usp_ as a prefix you don't get to use this handy feature.
Just a personal prefix that I like to use rather than "usp_" is "up_" this way I can perform a find for every occurance of "up_" to find all user procedures in a large script, "sp_" to find all system procedures in a large script and "p_ to find all procedures, user or system, in a large script.
But my personal preference for any standard naming conventions is to simply have one and have it documented. Unless Microsoft decide to set it in stone as part of the SQL code, no-one will ever agree on a common convention across the board but as long as we have a convention and have it documented this can make it all the more easier for outsiders or fellow co-workers to navigate our work and save far more time than it costs.
Charles - I agree 100%. There are many workable standards out there - but the best one is having one and sticking with it.
At first I was worried about putting my pet standard out there in this article, but I'm now glad I did it, because I think it has produced an interesting discussion. Also, I'm seeing that no one standard solves every potential situation - as almost every comment on this article has a point-counter-point.
In our environment SQL development is divided between 3 groups and about 1/2 "freelancers".
Two of the groups and all of the freelancers have no convention.
In my team we following something like the function_subfunction_Description_Parameter format.
I set the function
UI - stored procs used to deliver functionality to a user interface
MAINT - used to perform application maintenance functions
REPORT - used for reports
The sub function is something like AR, ORDERS
The Parameter would CURRENT month, etc.
Its not documented well, but the three or four us work well together on it.
I think it is a decent article at least for newbies. I had no naming convention and haven't had time to develop one as i work on this project. Having SOME IDEAS verses NONE is better than none at all. And this has been a worry on my part but i had no where to begin.
This discussion has also cleared up some things that are found in the books i've read taht do suggest using another prefix other than sp_.
Since i'm programming i do feel i need to be able to tell the difference between an SP in code an other variables or constants. Knowing for sure it is an SP can be valuable.
I will give more thought to my naming convention now even if i haven't decided how to implement it.
Say you have a database with 100 tables, with 4 procs each, 400 stored procs beginning with usp_. Your eyes quickly start to filter out the usp_. That prefix is noise. Save the characters for something useful, or just keep your proc names short.
System_Action_Object descriptor or just Action_Object_descriptor for a database used for just one module/system/subject